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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
602
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,384
Messages
5,510,985
Members
408,821
Latest member
OxfordSpires

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top