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??????
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top