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