Weird problem adding times using 1904

mayday1

Board Regular
Joined
Oct 5, 2007
Messages
241
Columns B,D,F,H,J,L and N are positive times in hh:mm format - basically the amount of time someone worked overtime for each day of the week. So if Bob stayed an hour and 45 minutes late on Monday, D3 would be 01:45.

Columns C,E,G,I,K,M and O are the times people left early on those same days. So if Bob left 3 hours early on Saturday, O3 would be 03:00.

In column P I do a weekly total like this:
=IF(SUM(B3,D3,F3,H3,J3,L3,N3)-SUM(C3,E3,G3,I3,K3,M3,O3)<>0,SUM(B3,D3,F3,H3,J3,L3,N3)-SUM(C3,E3,G3,I3,K3,M3,O3),"")

I don't show zeros so if the total of the overtime and leaving early is zero the cell in column P is blank. The math works fine but some some reason, certain values in B though O that equal zero are displayed in P as 00:00 rather than displaying a blank cell. I have 1904 time values turned on.

D=01:45 'overtime
F=00:45 'overtime
H=00:15 'overtime
K=00:15 'left early
0=03:00 'left early

I can plug in other numbers that equal zero in the same row and P is blank. This is really confusing. It's got to have something to do with which time values I'm adding and subtracting, but what??????
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
603
Your problem is that the sum of overtime is greater than 1 hour. I'm not smart enough to tell you WHY that is but I had the same problem. Never could figure out a way to fix it. Something is happening when you subtract times that are over 60 minutes.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,978
Messages
5,526,014
Members
409,675
Latest member
audeser

This Week's Hot Topics

Top