Time Table is giving me trouble

davsank

New member
Joined
Feb 12, 2013
Messages
1
Reaction score
0
Points
0
Location
A, A
Hi guys,
I'm sorry if this thread belongs at another forum, it just seemed as the closest one.

Anyway, I'm trying to build a not so regular time table (as where I come from breaks and such work differently) so basically, I got columns for dates(A) , day names(B), punch-in hours(C), punch-out hours(D), total work hours(E) and additional hours(F) and a few others. (sorry if I use the wrong terminology, I'm translating from Hebrew).

The formula I'm using to calculate the work hours is "=IF((D2-C2)<0,D2-C2+24,D2-C2)" so that it can also take into account night shifts or any shift that ends after 24:00.

the problem come at the very very last row called totals. the total for the work hours should show the work hours for the entire month, so I tired using "=SUM(E2:E31)" however it resets on 24:00 (so if someone worked 25 hours in a month for example, it'll only show 01:00).

How can I fix this?

I'm adding an Example File called Example.xlsx for may 2013 as this is the soonest I'm going to start using this - I'm using Excel 2010 if it's relevant.
 

Attachments

  • example.xlsx
    11.8 KB · Views: 15

Kevin@Radstock

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
365
Hi davsank

Format the total cell as [h]:mm. Format cells > Number > Custom > Type: "[h]:mm" (not inc. quotes)

Kevin
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
You can also simplify your formula to

=MOD(D2-C2,1)
 
Top