Converting negative time in Excel

March 17, 2009 at 12:21:26
Specs: Windows 2000
I'm trying to add time entries formatted as HH:MM in Excel including some negative values used as time corrections. There are formulas that convert time to decimals, but they fail on the negative values. Help.

See More: Converting negative time in Excel

Report •


#1
March 17, 2009 at 12:59:24
A bit more info, don't quite follow what your doing.
Do you have an example?

MIKE


Report •

#2
March 17, 2009 at 14:36:59
I have a list of users with their cumulative hours in HH:MM format. There also are negative values presented. The formula used to convert the hours to decimal values works on the positive numbers, but errors on the negative numbers.

E.G.
HRS
8:00
5:00
-2:00
-1:00
6:00


Report •

#3
March 17, 2009 at 15:27:44
How are you entering negative time values with a HH:MM format?

If I format a cell as HH:MM and try to enter -2:00, Excel pukes and tells me "The formula you typed contains an error"


Report •

Related Solutions

#4
March 17, 2009 at 19:21:10
Sorry for the confusion. The cell is not formatted as HH:MM, the value is input as HH:MM and is shown as a negative like -1:30 (1 hour, 30 minutes negative value). The values are from a time reporting application and show all entries, positive and negative.

Report •

#5
March 17, 2009 at 20:37:27
As far as I know, you can not input any negative number into a TIME formatted cell.

If you are inputing the data from an outside program, what may be happening is that those digits formatted as TIME with a colon, i.e. 5:30 are formatted as TIME and those digits formatted with a negative sign, i.e. -1:30 are being formatted as a NUMBER such as -1.30.
Could this be what's happening?
Do the negative sign cells have a colon or a period?

MIKE


Report •

#6
March 17, 2009 at 22:03:28
I'll test your theory about formatting. The negative symbol is simply a "-" in front of the number.

Thanks for your help.

Report •

#7
March 18, 2009 at 05:02:54
Jerry, the reason I'm asking all these questions, is that you can CUSTOM FORMAT the cell to Display the number as negative, it's not really a negative, it's just Displayed as a negative.

Something like - H:MM

There is a second way to calculate a negative, but it involves changing the way Excel determines time values. Something I would not advise.

So if we had a bit more information as to what it is your trying to do we might be able to help more.

MIKE


Report •


Ask Question