Format to display in excess of 24 hours

[Snaggles]

I have a time sheet in which I need to show a daily running total of hours worked.

The attached sheet is formatted in such a way that times can be entered in military time, i.e. just four numbers without the colon. The colon is added automatically by the format of those cells. The time calculations all appear to work as far as adding up daily hours etc. The problem appear in column J which should show the total weekly hours worked as every days times are added to the sheet. All works well until the total hours exceed 24. After that all sorts of weird figures appear. The green highlighted column shows the expected results.

I've tried all sorts of different formats for column J but once the total exceeds 24 it all falls apart.

Can anyone point me in the right direction please?

Thanks,
Lee

[PFDave]

Your values for time do not have the semi colon between and are written as 800 1200 etc.

I'll amend on your template a formula to factor this in but if you change those to time values you can simply use

=SUM(D2-C2)+SUM(G2-F2) in I2 and drag down

[PFDave]

Clean data in = clean data out

[xlnitwit]

Hi,

If you format column I as [h]:mm you will see where the results in J come from. It is unclear to me what you think that these two parts in the I2 formula are for
+(D2>-C2)
+(G2>-F2)

but all they achieve is to add two days to the result.

[Snaggles]

Hi PFDave,

Thanks for your help.

The reason that times are shown 800, 1200 etc. are as I said in my post... so that users can simply enter 4 digits for the time and not have to mess about entering a colon. Those cells are custom formatted as "00\:00" which displays the time as if it had been entered with a colon.
I realise it would be far simpler if users would enter times in the correct format, but they dont! Hence the formulas to convert the entries into "proper" times before doing the calculations; that all works.

Column I does work and seems to handle all the calculations correctly. It's only the addition of all the hours in column J where things fall over. That's the part I can't figure out.

[Snaggles]

Hi,

If you format column I as [h]:mm you will see where the results in J come from. It is unclear to me what you think that these two parts in the I2 formula are for
+(D2>-C2)
+(G2>-F2)

but all they achieve is to add two days to the result.

The reason for those bits is to handle the calculations correctly where a start time falls before midnight and the end time falls after midnight. If those bits are omitted the calculation doesn't work.

[PFDave]

Sorry but without a valid time format I can't make this work for you.

The entry for 8:00 as 800 can be converted to the right value using SUM(800/100/24), however as there are entries for 7:30 as 730 (as hundreds should be 750) this will not calculate.

If the users really have an issue entering clean data I would personally force each input with a userform to ensure it is entered correctly.

Sorry again I can't be of any use

[xlnitwit]

The calculation doesn't work anyway since D2 will always be greater than negative C2.

[xlnitwit]

Column I does work and seems to handle all the calculations correctly. It's only the addition of all the hours in column J where things fall over. That's the part I can't figure out.

As I mentioned above, column I does not in fact work. Format the cells as [h]:mm and you will see that 48 hours has been added to each cell.

[xlnitwit]

I think that this version may work for you.

[Snaggles]

Just come back to this little puzzle and have seen your last post and attached sheet... thanks.

Had solved the problem myself with a little lateral thinking... along similar lines though.