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.

A bit more info, don't quite follow what your doing.

Do you have an example?MIKE

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

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"

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.

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

I'll test your theory about formatting. The negative symbol is simply a "-" in front of the number.

Thanks for your help.

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

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History