**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.

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:

- 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.)
- Click Advanced at the left side of the dialog box.
- Scroll through the options until you see the section called When Calculating This Workbook. (See Figure 1.)
- The Use 1904 Date System check box controls which dating system is used, just as in earlier version of Excel.

** Figure 1.** The Advanced options of the Excel Options dialog box.

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**.

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

When you are formatting your worksheet, Excel lets you easily merge adjacent cells together. If you want to wrap the text ...

Discover MoreWhen your macro checks the formatting used for a cell, it needs to be careful that the type of formatting being checked ...

Discover MoreAs you are formatting a worksheet, Excel allows you to easily add borders to cells. Adding rounded corners to cells is a ...

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

2019-11-13 05:08:18

Chris

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

2019-10-17 03:34:33

Lucy

2019-09-10 11:38:23

Valeria Jones

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

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;-[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

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

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

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

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

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

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

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

"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

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

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

=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

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.

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

Copyright © 2019 Sharon Parq Associates, Inc.

## Comments