Adding hours for split shifts

Hi Fingus,

To get the number 10 from 10:00, simply multiply the time by 24. 10:30AM x 24 = 10.5, for example.

Your "total hours" might be 10:00, but if you select that cell it will most likely show "10:00:00 AM" in the function bar above.

Times are stored as fractions of 1 day, so all times are between 0 and .999. If you format a cell that has a time in it as a number with decimals, you'll see the decimal equivalent of the time shown.

10:00:00 AM is equivalent to 0.416667.
10:30:00 AM is equivalent to 0.4375.

If you multiply either by 24 you will get the correct number of hours for your calculation.

0.4166667 * 24 = 10
0.4375 * 24 = 10.5
etc..

As mentioned in a previous, although now missing, post, you could also use

=HOUR(A1)

where A1 holds the total time in hh:mm format.

=HOUR(A1) where A1 = 10:00 would result in just 10.

Originally Posted by Paul

As mentioned in a previous, although now missing, post, you could also use

=HOUR(A1)

where A1 holds the total time in hh:mm format.

=HOUR(A1) where A1 = 10:00 would result in just 10.

That was me.. but I deleted it realizing that there could be fractions of hours too..as you showed in your example, Paul.. Hour() only gives the hour portion and MINUTE() and SECOND() only give the minutes and seconds, respectively, as whole numbers

If you post a copy of the updated workbook (with your hidden columns) we can take a look..