Spreadsheet Queries

[Neutral]

Hi, im trying to set up a timesheet for work but am having some trouble Heres a pic of what im trying to achieve and failing miserably



I'm using openoffice

[nullmouse]

I don't know how to do it using proper times, but if you express times as hours plus minutes as decimal (e.g. 21.00 would be 9:00pm, 1.25 would be 1:15am) then this should work:

=((IF(C2<B2,C2+24,C2))-B2)

Where B2 = Start time, C2 = End time, as per your screen shot.

Obviously not ideal, but I don't know how to do it using actual times :/

I should say this works in Microsoft Excel, but should work in most spreadsheets - The IF formula is to adjust any times in am to be 24 hours greater than the pm start time, to allow subtraction to get the hours. This obviously assumes you don't work shifts of longer than 24h The second clause if the IF statement allows you to enter end times of before midnight the same day.

[RossP]

Originally Posted by nullmouse

I don't know how to do it using proper times, but if you express times as hours plus minutes as decimal (e.g. 21.00 would be 9:00pm, 1.25 would be 1:15am) then this should work:

=((IF(C2<B2,C2+24,C2))-B2)

Where B2 = Start time, C2 = End time, as per your screen shot.

Obviously not ideal, but I don't know how to do it using actual times :/

I should say this works in Microsoft Excel, but should work in most spreadsheets - The IF formula is to adjust any times in am to be 24 hours greater than the pm start time, to allow subtraction to get the hours. This obviously assumes you don't work shifts of longer than 24h The second clause if the IF statement allows you to enter end times of before midnight the same day.

Any chance you can take my Higher Admin classes later in the year so I can go to Macedonia and Iceland for the Scotland games??

[Frosty Jack]

Firstly you need to make sure the relevant columns are in the Time format.
Select columns B, C & D by clicking on the column headers, then right click and select 'Format Cells'. Under the first tab (Number) select 'Time', with the format xx:xx. Then when you enter the time use the format xx:xx with a colon separating the hours from the minutes.
Now use the following formula in column D -
=C2-B2+IF(B2>C2,24)

Everything should now be calculated in hours and minutes. The IF function allows for when your shift goes past midnight i.e. if your end time is a smaller number than your start time.
N.B. this is how it works in Excel, I'm presuming OpenOffice will be the same or similar.

[nullmouse]

Originally Posted by Frosty Jack

Firstly you need to make sure the relevant columns are in the Time format.
Select columns B, C & D by clicking on the column headers, then right click and select 'Format Cells'. Under the first tab (Number) select 'Time', with the format xx:xx. Then when you enter the time use the format xx:xx with a colon separating the hours from the minutes.
Now use the following formula in column D -
=C2-B2+IF(B2>C2,24)

Everything should now be calculated in hours and minutes. The IF function allows for when your shift goes past midnight i.e. if your end time is a smaller number than your start time.
N.B. this is how it works in Excel, I'm presuming OpenOffice will be the same or similar.

Sweet! I now know why I couldn't get it to work using 24h, I'd not changed the "Total Hours" column to be formatted as 'Time' as well, so it was returning values of '23.17708' instead of 04:15. I also made the 'IF' clause more complicated than it needed to be