Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Displaying Negative Times.

# Displaying Negative Times

by Allen Wyatt
(last updated February 17, 2020)

Mike has a worksheet that contains some times. If he subtracts 6:33 from 6:21 he noticed that he doesn't get a negative elapsed time, as in -:12. Instead he gets ############# in the cell. Mike is wondering how he can display the negative time difference properly?

The easiest way to solve this problem is to just change the date system used by the workbook. This may sound goofy, but if you use a 1900 date system (which is the default for Windows versions of Excel), then you get the ############# in the cell. If, instead, you change to the 1904 date system (which is the default for Mac versions of Excel) then you'll see the correct negative elapsed time in your formula.

To change the date system, follow these steps:

1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 or a later version, display the File tab of the ribbon and then click Options.)
2. Click Advanced at the left side of the dialog box.
3. Scroll through the options until you see the section called When Calculating This Workbook. (See Figure 1.)
4. Figure 1. The Advanced options of the Excel Options dialog box.

5. The Use 1904 Date System check box controls which dating system is used, just as in earlier versions of Excel.

If you prefer to not change the dating system used in the workbook (perhaps it may mess up some other date formulas you have in the worksheet), then the only thing you can do is to create a text-based version of the time differential using a formula such as the following:

```=IF(B2-A2<0, "-" & TEXT(ABS(B2-A2),"hh:mm"), B2-A2)
```

If the difference between the two time values (in A2 and B2) is negative, then the formula concatenates a text value consisting of the minus sign and the absolute value of the difference between the times.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6239) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Displaying Negative Times.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Filling References to Another Workbook

When you create references to cells in other workbooks, Excel, by default, makes the references absolute. This makes it ...

Discover More

Understanding the No-Width Characters

Search through the Symbol dialog box, and you may see some special characters whose names seem odd. These are the ...

Discover More

Multiple Document Users

If you have a group of people working on a single document, you may wonder what tools are available in Word to facilitate ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

##### More ExcelTips (ribbon)

Using Fractional Number Formats

If you want information to display on the screen using fractions instead of decimals, you're in luck. Excel provides ...

Discover More

Exporting Latitude and Longitude

A handy way to store latitude and longitude values in Excel is to treat them as regular time values. When it comes around ...

Discover More

Select One Cell and Make Another Cell Bold

Excel provides a number of different ways you can apply formatting to a cell based upon various dynamic conditions. One ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight more than 0?

2021-01-14 21:17:02

May Phue Thet Su

Thanks a lot.

2020-12-06 04:42:11

Roy

Actually, using the 1904 system isn't a panacea as it contains a difficulty you may not notice until too late.

Yes, it will display a time, all by itself, no date included. So that's all good. But, use that as a date and time and something different happens. And guess what trying to use it in further time math does? Yes, uses it as a date. Understand, this IS in the sense of displaying it, but it will report the date and convert the time portion to, let's say, 12:12 AM, vs. the format you began with. So: 1/1/1904 12:12 AM vs. 00:12. Subtract twice that back off to return to the -00:12 and you return to -1/1/1904 12:12: AM. So not only is there that practical difficulty, but notice there's one more.

The 1904 system doesn't do negative dates. Oh, it looks like it does, sure, got that "-" in there. But it isn't there for the"dd" portion. It's just there, I guess to let you know the whole thing went negative. Certainly not to show a negative day, or month, or anything. I might understand, for instance, if they chose that display rather than the rather odd-looking "01/-01/1904" so that didn't have to be a knock but... that's not what's happening.

What's happening is that the 1904 system does NOT do negative dates. It shows the negative sign, but moves back forward. Think of how a quadratic's graph goes to a minimum (or maximum), then turns and goes back the way it came. So -2 gets "-01/03/1904" and so on. So that display's gonna get confusing nowadays. Not to mention if you try to use portions of the date you can extract.

So, not a panacea.

2020-09-29 11:01:48

Mark S (London)

Thanks, this 'Use 1904 data format' was very obscure and I'd never have worked it out.
A big help !

2020-09-17 08:52:18

Shrawan Mishra

Hi,
after deducation 00:00:00 (time diffrence) also showing negative sign. How to remove that from zero hours difference? Pls suggest.

2020-06-30 11:28:33

Jane

Maybe this is something I should have known, but you Allen Wyatt are currently my favourite person!! I HATE using a sledgehammer to crack a nut, so I LOVE this :) :) :)

2020-05-19 16:46:37

Peter Atherton

Sara
DateDif produces text, maybe this:
=IF(Date_today>Due_Date,(Date_today-Due_Date)*-1,Due_Date-Date_today)

(see Figure 1 below)

Figure 1. Showing Negative Date

2020-05-18 17:39:50

sara

How about applying this principle to two dates?

I have two columns each with dates (E and V) and calculating the difference between the 2. If date in one of the columns is less than the other, i want it to appear negative. I had been using this equation: =IFERROR(DATEDIF(E3,V3, "D"),DATEDIF(V3,E3, "D")). How can i incorporate the negative sign if column E date comes before column V?

2020-02-05 06:00:06

lauryns

Thanks !

2019-11-13 05:08:18

Chris

Converting the time value to hours (simply multiply by 24) for display purposes worked better for me than changing the date system (which changed all the dates in my sheet by four years, untenable). The only disadvantage is that parts of an hour are shown as decimals rather than minutes.
Background:
I was working with a table of time differences between time zones, which involves a range of values between +14 (Line Islands time) and -12 hours.

2019-10-18 10:59:22

Mick

THANK YOU!! It is truly amazing how many people and websites out there do not understand Excel date/time formatting. There are some terrible tutorials and examples out there. This answered my question in about 30 seconds.

2019-10-17 03:34:33

Lucy

Thank you so much for this - precisely what I was looking for, and didn't know existed. That's a life hack right there!

2019-09-10 11:38:23

Valeria Jones

Thank you. This small change was exactly what I needed. I had calculated times that I need to have added together and then subtracted from a total time to see how many more work hours were needed, but I also needed to see when work hours had exceeded the total and comp time would have to be taken. I was getting #### expressions anytime it dipped over by 1 minute. Now I can see the negative time and that lets me know to add some free time to another work day to balance it out.
This fix was much easier than I expected. ( I hope it doesn't mess up any other worksheets... :) )

2019-05-06 11:53:48

Roy

Huh. I can't seem to generate any problem with this.

Simply do the subtraction as "a - b" or "b - a", whichever fits ones situation but wrap it with ABS() so there is no negative involved. Elapsed time is the concern, not which direction it elapsed. Specifically, for the problem Dimitris describes, it cannot be a "real" negative since the ship is not able to unload before it docks... So, since the result being "negative" or "positive" is simply an artifact of how one set up the calculation, and not the underlying reality, removing it from the result will not end up ruining the result.

Then the "dd:hh:mm" formatting is applied and always works, regardless of date system, since it is formatting a positive result.

Lastly, if one has a strange situation in which, say for the problem below, the lay time has to be an elapsed time, but the ship has a day free, for unloading, say, and finishing earlier has to be recorded as a negative time (finishes in 22 hours, was allowed 24 hours, so result has to be -2 hours), one can still do all the math as above (modified to add the "free time for unloading" aspect), still wrapping that final result in ABS(), including in an IF() test to see if it was negative before the ABS() was calculated by Excel, and output it as TEXT() using the above format for times that exceeded the 24 hours, or adding a negative sign before the format for negative times.

Doesn't seem likely that last would usually be the case, because most instances of it would seem to have an element of stupidity to them (in real life, those ships aren't getting paid by the port for unloading more quickly than the 24 hours...). Organizations though... well, their dictionaries do not begin the definition of "stupidity" with the word "anathema" so one might need to work with that. But mostly, it would seem the last paragraph would very seldomly be needed.

Another last thought, about TEXT()-ing the result. Bringing the result "back" to being a number, for further calculations, is problematic as Excel by itself, of nudged by TIMEVALUE(), will bring it back as hours:minutes:seconds, not days:hours:minutes, so one would either need to consider that and disect the value for proper returning to a number, or perhaps simply redo the calculation ending with a number, before ABS(), for the further work. (If one did that with a Named Range, that part could be readily available to both the main work AND the follow-on work.) If it has to come back to being a number at all.

2018-02-12 19:30:57

Jonas

[t]:mm;-[t] works with "1900" but will not return minuts. -1:30 will be -2
[t]:mm;-[mm] returns total minuts
[t]:mm;-[t]:mm not working for me to get right negative return with both hours and minuts. Think it need a update.

2017-01-04 04:39:39

jaafar

thanks a lot, this is very interesting

2016-10-16 11:13:35

student

thank you for the solution, it helped me!

2016-09-06 12:37:24

Dimitris

Hello Again Mr. Chris,

The cell D44 contains the number :03:15:35 (dd:hh:mm)

The cell D45 contains the number :01:20:51 (dd:hh:mm)

So the cell D46 has the result :01:18:44 (dd:hh:mm)

When the cell D45 is greater than D44 I get in cell D46

######

When I use the format 1904 there is not ay problem in

the final solution even D44 is greater or less the D45.

But the problem has to do that using the form 1904

mess up other dates in the programme. .

2016-09-06 12:26:31

Dimitris

Hello Mr. Chris,
Could you give me please a little bit clarification over the solution you proposed below.
You mentioned :
For example, turn your value into a number of hours, calculate, then convert back to dd:hh:mm.Zero will not matter and you can handle a meaningless negative sign however is needed.

2016-09-06 11:52:48

Chris

Say Dimitris,
Since you are not actually calculating the difference between dates, you are processing an arithmetic expression. As with any engineering expression, simply make the units consistent and then change to whatever units you prefer when the calculation is complete.

For example, turn your value into a number of hours, calculate, then convert back to dd:hh:mm. Zero will not matter and you can handle a meaningless negative sign however is needed.

2016-09-05 10:04:55

Dimitris

Dear Mr. Andy Good Day,

Thanks a lot for your prompt reply. I guessed that the format I used was uncommon, but due to professional reasons I would like to use that one. I followed what you wrote me but I got the below answer from the Excel after pressing the Enter button.

The formula you typed contains an error :
˙To get assistance in entering a function, click OK, then click Function on the inset menu.
˙If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-) or procede it with a single quotation mark (')

As I told you I am suffering for finding solution for that programme. The programme has to do with Lay Time for Ship's Discharging or Loading. The Lay Time is calculated in days, hours and minutes. There is nothing to do with a calendar month, for instance whether is January, February, etc. Just it counts days, hours and minutes. For that reason I made that formula. Never Lay Time exceeds 31 days, at least as far as it concerned the today situation.

I tried many times with the solution you proposed me, I also tried to use a single quotation mark but it didn't work.

I have a quantity of cargo, 33,000 MT and discharge rate is 8,000 per day. The numbers 33,000 and 8,000 are in different cells and don't enter in cells D44, D45.

So after the calculation I got the result in D44 : 4,125 then I formatted this number in days, hours and minutes. In cell D45 there is another calculation giving different result than D44, but I formatted this cell in days, hour and minutes as well. Now when the number in D45 is greater than D44 I have the problem with negative result and ########. When the result is positive D44>D45 I got the result in days, hours and minutes without problem even the formula I use is not in common.

Could you have an idea how I can solve this password?

Thanks in advance for any kind of assistance.

2016-09-03 15:40:34

Andy

Dimitris,

I use a PC but assuming a Mac works the same way.

I would first say that dd:hh:mm is an unusual format to use as the value of 'dd' will depend on the month (so can range between 1 and 28, 29, 30 or 31). The content of your cell D45 starts with zero which is not a legitinate calendar day. Although a cell may be formatted to look like a time the underlying value is a number with zero being 00/01/1900 which is nonsense as a date as years start on 1st Jan.

On my PC, formatting a cell as dd:hh:mm and then typing in 03:15:30 is interpreted as a time i.e. 3:15am because Excel recognises and expects times to be composed of numbers and colons.

If you want to have differences in times expressed in hours Excel provides the format [h]:mm:ss but the underlying value which Excel stores for 3d 15h 30m is 3/1/1900 15:30, which can be displayed using [h]:mm:ss as 87:30.

If your question had been relating to cells formatted hh:mm:ss then the displayed values can range between 00:00:00 and 23:59:59 although the underlying values stored in Excel may include days, months and years if these values have come from calculation.

If you are typing in times and the first time is 15:30 and the second (later) time is 10:52 and you want to calculate the difference then your Cell D46 would need to be:

=IF(D45>D44,D45-D44,1+D45-D44)

The '1' is added because Excel's underlying units of time are days.

The key to working with time differeces is recognising how Excel stores dates and times. Where there is a risk of a calculation giving negative times and you want to display these I would recommend including cells in your spreadsheet which are only used for display and perhaps hide the cells containing the pure numeric values which the spreadsheet is using for its calculations.

So going back to your example:

In Cell D46 you would have

=D45-D44 (which gives -3.1930555)

But to display this in a dd:hh:mm format in a new cell you can turn it into text. So in Cell D47 we could have:

=IF(D46<0,"-","")&TEXT(ABS(D46),"dd:hh:mm")

which will show as -03:04:38

This works provided the number of days difference is less than 31. Excel uses 'dd' as the number of days in a month so 32 would become 1 as day 32 is 1/2/1900. If the number of days difference does go above 31 then the whole number in front of the decimal point is already available so get it using TRUNC. And you can turn the decimal part into hh:mm.

=TRUNC(D46)&TEXT(ABS(D46),":hh:mm")

This isn't perfect as it doesn't deal with the number of days being zero. This does:

=IF(D46<0,"-","")&TRUNC(ABS(D46))&TEXT(ABS(D46),":hh:mm")

2016-09-02 16:20:37

Dimitris

Hello Sir,
Please could you assist me in my below calculation because I am struggling 20 hours and cannot get it using many formulas.
I have this formula : Cell D44 : 03:15:30 (where dd:hh:mm) - Cell D45: 00:10:52 (where dd:hh:mm). I want to get the result in the cell D46, in case that D45 is greater then appears ####### I tried many solution but they didn't work.
I used the form 1904 and got the result without negative but I don't want to use this form because mess up some other date formulas. II use Excel 2011 in Mac computer.
Thanks a lot in advance.
dd=days
hh=hours
mm=minutes
I custom the above formula dd:hh:mm because I want to use it for my work, there is not such a formula in Excel, I am not sure whether it can work or not, for the reason that I cannot get result without negative.

2016-08-01 03:00:24

Dave

I have a spreadsheet which calculates my hours spent at work. Column F tells me whether I accrued time or used time, and therefore can sometimes be negative.
I want Column H to respond when Column F is too far negative.

K11 is a negative time. If F26 is greater than K11, I want H26 to say "Yes".

So... =IF(F26<K\$11,"Yes","")

This formula gives me a "Yes" in H for any negative time.

And... =IF(F26>K\$11,"Yes","")

This formula always returns nothing...

What can I do so that when F is -2:45 and K is -1:00 I get a "Yes" in H but when F is -0:15 and K is -1:00 I get nothing in H?

Many thanks! :)

2016-05-27 06:06:47

Marks

Hello, I have a similar but different problem.

I must conditionally format a cell with hour 0:00 format.
It must accept negative values, so I've set the "1904" system.
Basically if the value is <0:00 then the cell BG must be red, if it's =0:00 it must be light green, if it's >0:00 it must be dark green.

Upon specific circumstances, I happened to get a -0:00 value, which went red, so I've set the "precision as displayed" option to get rid of the meaningless "-".

The problem is... I can't find a way to get the light green, it always goes to dark green, either on =0:00's or >0:00's.

I'm stuck here guys :-|

2016-01-06 08:32:27

Suzanne

Used the 1904 System check box option and it worked a treat. Thank you.

2015-12-31 05:36:53

Agi

I am using the if formula advised above and it is great. I just have one question. I wand to change my result to format:
"Average" dd "Days" hh:mm:ss

Could you please advice how can I incorporate it in the above if formula?

Thank you in advance for your help.

2015-12-14 07:15:52

Gregg Clark

Your Excel working with negative times was a great help only wish I'd found this site 3 years ago. Now my time sheet requires not second guessing. Many thanks!

2015-10-25 17:47:25

vicsar

Thanks, this was very useful. It did get me started to solve a greater problem.

2015-10-21 09:20:47

Michael (Micky) Avidan

@freddy lemmens,
In your case the formula could be shorten - try the following:

a) =B3-A3+(A3>B3)
b) =MOD(B3-A3,1)

---------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

2015-10-21 05:46:03

freddy lemmens

or use this formula
=B2-A2+IF(A2>B2;1)
B2 = end time
A2 = start time
This way the result stays a time, so you can add up or perform other calculations.

2015-10-20 13:36:17

Kate

This was so helpful after searching tiredlessly through help and on the internet it took less than 5 minutes after I read this tip.

##### This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.