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

#### Flopem123

##### New Member
Hi all

I'm trying to calculate a number of cells, which all have a time in them.
 Contracted 37:00:00 Hours Worked 5:27 Credit from Previous Week 3:25 Debit from Previous Week 0:00 Total 28: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

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### joeu2004

##### Well-known Member
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
=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
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.

Replies
8
Views
223
Replies
6
Views
251
Replies
2
Views
141
Replies
2
Views
111
Replies
4
Views
298

1,119,188
Messages
5,576,590
Members
412,736
Latest member
LODS