Cell turns ####### when time turns negative

Flopem123

New Member
Joined
Aug 31, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi all

I'm trying to calculate a number of cells, which all have a time in them.
Contracted37:00:00
Hours Worked5:27
Credit from Previous Week3:25
Debit from Previous Week0:00
Total28:08
The time calculation is fine when the time is positive but as soon as the time goes below zero I get ######## in the total cell. I did find a formula (below) which worked for subtracting one time from another but as I have a third cell which needs to be included in the calculation I cant seem to get it to work. I have tried adjusting the formula but nothing seems to work.

=IF(R2-R3<0, "-" & TEXT(ABS(R2-R3),"hh:mm"), R2-R3)

Hope you can help

Thanks
 

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,)

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,905
Office Version
  1. 2010
Platform
  1. Windows
Try:

=IF(R2-SUM(R3:R5)<0, TEXT(SUM(R3:R5)-R2, "\-[hh]:mm"), R2-SUM(R3:R5))

formatted as Custom [hh]:mm and Horizontal Alignment right.

The square brackets around [hh] is recommended for any time calculation, just in case the result is 24 hours or more, as your example is.

But beware that the "negative" result is text, not a number. You cannot reference it directly in calculations in other cells. LMK if that might be a problem. There is a solution.

IMHO, resist any suggestion to set the 1904 Date System. Although that does allow for negative time as a number, it might screw up other things on PCs, especially in an existing workbook and when copy-and-pasting dates between workbooks.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,905
Office Version
  1. 2010
Platform
  1. Windows
=IF(R2-SUM(R3:R5)<0, TEXT(SUM(R3:R5)-R2, "\-[hh]:mm"), R2-SUM(R3:R5))

I just noticed that R5 is labeled "debit", which might be added, not subtracted. So you might write:

=IF(SUM(R2,R5)-SUM(R3:R4)<0, TEXT(SUM(R3:R4)-SUM(R2,R5), "\-[hh]:mm"), SUM(R2,R5)-SUM(R3:R4))

Although you do not need to use the SUM function here, I use it to emphasize the "need" (best practice, IMHO) to collect terms to add and terms to subtract.

And of course, it would be ever simpler if SUM(R2,R5)-SUM(R3:R4) were calculated in a "helper" cell (possibly hidden).
 

Flopem123

New Member
Joined
Aug 31, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi Joe, The second formula worked great. Your correct I have a credit and a debit field and the debit it added rather than subtracted.

I have seen a number of posts regarding the 1904 option but was reluctant as this timesheet will be used by about 10 people.

Thanks for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,118,294
Messages
5,571,372
Members
412,385
Latest member
OChambo94
Top