Working with times in Excel

[ccordner]

Good Afternoon, I have a slight problem.

I have a worksheet, showing start times, finish times and hours worked for each day of the week. At the end of the week, a separate cell tots up the total hours worked during the week.

If you imagine that cell C12 shows their shift start time for the week, Cells G12, H12....L12 (Sundays are different) are linked to show whatever C12 shows.

Cells G13, H13...L13 have a formula similar to this:

=IF(G12>0,G12+$C$15,"")

Where C15 shows that person's daily hours (Normally 7:00, 7:30, 7:47 or 8:45).

Cells G14, H14...L14 have a formula similar to this:

=IF(G12>0,IF(G13>G12,G13-G12,G13+(24-G12)),"")

Finally, Cell N12 has the formula:

=SUM(F14:L14)

All is well, even when the finish time is after midnight until somebody starting at, for example, 13:20, works overtime.

The system would show them finishing at 20:20, so this is manually overridden to 0:24, their actual finish time. Everything works fine, except Cell N12, which adds about 500 hours on!

Curiously, it doesn't do this if you type in "24:24" instead of "0:24", which is odd!

Any ideas why?

Chris

 

[GlennUK]

Without analysing what you're doing, this just caught my eye:

=IF(G12>0,IF(G13>G12,G13-G12,G13+(24-G12)),"")

... that's 24 days worth of time that I've highlighted ... surely that should be 1 ( 1 = 24 hours timeserial )?

 

[ccordner]

Fantastic!

Thanks!