Jump to content
aberdeen-music

Spreadsheet Queries


Guest Neutral
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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??

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 :D

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...