How to display a negative time in Excel 2003 1

[nghiapp]

I would like to display result of a subtraction between two time values in time format "hh:mm". It is OK if the result is positive, but only ###### if negative. How to format the cell to show the result with "-" sign as "-12:23" ?

 

[xlhelp]

1904 date system will let you calc negative time. Existing dates in workbook change by 4 years and a day.

Else, you can error out negative time and only calculate absolute value with another cdell indicating (through error) whether the data is -ve or +ve.

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

This is a well established problem with Excel's 1900 date system. It cannot display negative times.

There are a few work-arounds.

[tab]-Use the 1904 Date System
Go to Tools > Options > Calculation and tick the box beside 1904 Date System.
[!]NOTE[/!]: If this file is opened on someone else's computer, it will not display properly. Also - and more importantly - any file you share with pretty much anyone else that contain dates will display different dates on your computers. For this reason, I'd recommend NOT using this solution.

[tab]-Calculate the absolute value of the difference
=abs(A2-A1) will return the correct amount of time. The problem is that it doesn't read negative. You can use custom formatting to give a visual queue that it is negative if you'd like (based on the match returning a negative value)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

More options:

1) If (and only if) you are not going to do any more math with the resulting time, then you can use a formula like the one below to return a text string that looks like the answer you want.

=IF(SmallTime-BigTime<0,TEXT(ABS(SmallTime-BigTime),"[h]:mm") & " (Early)",TEXT(SmallTime-BigTime,"[h]:mm") & " (Late)")

or

=IF(SmallTime-BigTime<0,"-" & TEXT(ABS(SmallTime-BigTime),"[h]:mm"), TEXT(SmallTime-BigTime,"[h]:mm"))

2) Even though Excel doesn't display the negative time, the math is done and the serial number is stored (see faq68-5827 for info on how Excel deals with dates and times). Change the format of the cell containing the formula to see the result. Depending on what you are going to do with the info, this might be helpful.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[nghiapp]

Thanks everybody for the advices.
I'm trying to calculate flex time of a day. It shall be shown for every day and the accumulated sum of a week. I think I will use two cells of each day, one to show the value in text format and another one in number format to calculate the sum.