Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 August 1, 2015)

17

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 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 version 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, and 2013. 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

Converting Numeric Values to Times

If you have a bunch of times entered into cells without the colon between the hours and minutes, chances are good that Excel ...

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 no-width ...

Discover More

Fitting Text Into Cells

Need a way to make sure your text fits within the space available in a table cell? Word has a handy setting that will adjust ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

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

Removing All Formatting

Getting rid of formatting from a cell or group of cells can be done using several different techniques. This tip describes ...

Discover More

Printing a Multi-Line Footer

Add a footer to your document and you may want to make it span more than a single line. This can be easy to do, provided you ...

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.

Comments

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 8Mpixels. 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 6 - 3?

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.

Newest Tips
Subscribe

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.