Help req'd with formulas: how do I total up hours using 24 hr clock

[mgh65]

Hi,

I have a spreadsheet for my timesheets. I want to load in start times
and finish times in 24 format and have the formula work out the number
of hours, and also the number of hours of overtime (anything more than
12 hours)
I can't get a formula to give me this.
Any help much appreciated.

...Mike

 

[PhilD]

I have a spreadsheet for my timesheets. I want to load in start times
and finish times in 24 format and have the formula work out the number
of hours, and also the number of hours of overtime (anything more than
12 hours)

Format your time cells (apple-1, if I remember correctly) to 24 hour.
Easiest is to go to Custom, and format as hh:mm:ss. When you input
your times, type them in the same format, e.g. 16:30:00 for "half past
4 in the afternoon".

The sums should work out easily from there. So, if A1 contains
16:30:00, A2 contains 08:45:15 and A3 contains =A1-A2, then A3 will
return 07:44:45. Note that negative results will return an error.

Does this help?

PhilD

 

[JE McGimpsey]

Note that negative results will return an error.

Only if you're using the Windows XL default 1900 date system...

 

[JE McGimpsey]

I have a spreadsheet for my timesheets. I want to load in start times
and finish times in 24 format and have the formula work out the number
of hours, and also the number of hours of overtime (anything more than
12 hours)

1) You've already been given some suggestions for formatting 24-hour
time. I'd suggest going a step further - if you sum these hours and the
sum exceeds 24 hours, XL's formatting will "roll over" unless you put
brackets around the hours in the format:

[h]:mm

For instance:

A1: 12:00
A2: 11:00
A3: 2:00
A4: =SUM(A1:A3)

When formatted as h:mm, A4 will display 1:00. When formatted as [h]:mm,
A4 will display 25:00

2) To calculate the number of hours of overtime, one way is:

A1: 14:00
A2: =MAX(0, A1 - TIME(12,0,0)) ===> 2:00

when A2 is formatted as a time. Since times in XL are stored as
fractional days (e.g., 3:00 == 0.125), this can be more efficiently
written

A2: =MAX(0, A1 - 0.5)

 

[mgh65]

This is how i want to layout my timesheet in the excel spreadsheet (Mac
version)
Date start finish Hours overtime
11-Sep 08:00 21:00 1.00

So I don't need to actually calc total hours, just hours over 12.
so I just need a formula for the cell with the 1.00 in it to calc the
overtime hours.

Sorry if this post is confusing, but I'm new at excel and this posting
business too.

 

[JE McGimpsey]

This is how i want to layout my timesheet in the excel spreadsheet (Mac
version)
Date start finish Hours overtime
11-Sep 08:00 21:00 1.00

So I don't need to actually calc total hours, just hours over 12.
so I just need a formula for the cell with the 1.00 in it to calc the
overtime hours.

You apparently want the result in decimal hours rather than an XL time.
Since XL times are stored as fractional days, you just need to multiply
the result of the formula I gave you earlier by 24:

D2: =MAX(0, C2-B2) * 24

Format D2 as General, or a 2-decimal Number format.

 

[PhilD]

This is how i want to layout my timesheet in the excel spreadsheet (Mac
version)
Date start finish Hours overtime
11-Sep 08:00 21:00 1.00

So I don't need to actually calc total hours, just hours over 12.
so I just need a formula for the cell with the 1.00 in it to calc the
overtime hours.

Ah, if you format your cells as stated previously, then this should be
quite straightforward, as 12 hours just happens to be 0.5 days.

Suppose your data is in columns A to D, and the data in row 2. Cell D2
(where you want the 1.00 to appear) should contain

=C2-B2-0.5

That's it! You are stating "9 o'clock in the evening minus 8 o'clock
in the morning is 13 hours. Deduct half a day leaves 1 hour".

PhilD

 

[JE McGimpsey]

Ah, if you format your cells as stated previously, then this should be
quite straightforward, as 12 hours just happens to be 0.5 days.

Suppose your data is in columns A to D, and the data in row 2. Cell D2
(where you want the 1.00 to appear) should contain

=C2-B2-0.5

That's it! You are stating "9 o'clock in the evening minus 8 o'clock
in the morning is 13 hours. Deduct half a day leaves 1 hour".

One thing worth noting:

If your times could span midnight, e.g.:

Date start finish Hours overtime
11-Sep 20:00 09:00 1.00

You'll need to adjust for the finish time being smaller to XL (e.g.,
0.375) than the start time (e.g., 0.8). One way to do this is to use
the fact that XL treats TRUE/FALSE in math formulas as 1/0:

=(C2 - B2 - 0.5 + (C2 < B2)) * 24

(Again, the * 24 is necessary to get your desired decimal hours).

A somewhat obscure alternative would be to use the MOD function:

=(MOD(C2 - B2, 1) - 0.5) * 24

or, equivalently, and perhaps making the intent more clear:

=MOD(C2 - B2, 1) * 24 - 12


Also note that, while XL will, when the cell display formats are set to
"h:mm", display 0.875 as 21:00, there's nothing inherently associated
with that value that determines that it's to be interpreted as 9 o'clock
in the evening. It's simply a value, so XL will happily take the square
root of 9:00 PM and return 10:27 PM (i.e., SQRT(0.875) = 0.935414347).