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 =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
634
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,026
Messages
5,856,928
Members
431,838
Latest member
mydayisgood

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
Top