Input negative values in Excel

December 1, 2009 at 06:20:20
Specs: Windows Vista, 3gb
I need to input a negative time value into a time formatted cell in excel.
I have a cutom cell format: [hh]:mm;[Red] -[hh]:mm
This works perfectly for outputting a negative number from a formula, but I still can't input a number manually.
I need the number to work with the rest of my formulas, so cannot have it to just look like a negative number as a previous post suggested.

There is an obvious work around of getting the number from an export of a formula, but this is soooo long winded when all I want is to type in the number!

Is this possible? It seems so simple like it should be, but I haven't found any answers from google!


See More: Input negative values in Excel

Report •


#1
December 1, 2009 at 07:28:11
Hi,

All date/time values in Excel are positive.

As a result you will likely get "#######" in a cell formatted as a date/time with a negative value.

Try entering the time as a positive value and then in the cell that uses the value subtract it, i.e., subtract a positive value rather than adding a negative value.

I don't know what your formulas are, so I don't know if this suggestion is suitable. If you post more information with the formulas you want to use, maybe someone can suggest a better solution.

Regards


Report •

#2
December 1, 2009 at 07:41:00
Cheers for the reply, I don't get the "######" in the boxes due to the custom cell format which allows me to view negative numbers.

It sounds like your answer is the same as what I thought I would have to do. Which does indeed work, just seems silly to me!

My formuals are simple ones just to add up my work hours, as I'm on flexi time. In this example I had to take longer for lunch so tried adding in -00:20 when it came up with the error. The error box that came up suggested putting a single quote mark ' infront of the value, so I did this. But once I did this the value was not recognised as a number by my formulas, so my totals were wrong!

I just can't understand how it's possible for a formula to be able to produce a negative time, but it's not possible to input a negative time manually.


Report •

#3
December 1, 2009 at 08:26:01
The only other solution is to use the 1904 date system.

Select Tools,
Options,
click the Calculation tab,
check the 1904 date system box.

Negative times will now be displayed correctly.

Caveat emptor
Be very careful.....
if your workbook contains links to other files that don't use the 1904 date system then the mismatch of date systems will cause erroneous results.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
December 1, 2009 at 08:37:48
I tried your Custom format in Excel 2003 and still got the #### error, except that it was in red.

I put 1:00:00 PM in B1
I put 2:00:00 PM in B2

I formatted A1 as Custom: [hh]:mm;[Red] -[hh]:mm and entered =B1-B2.

The result is #### and when I hover over the cell, I see the message:

Negative dates or times are displayed as ####

I entered the same formula in a cell formatted as General and it returned:

-0.041666667

As soon as I applied your custom format, it changed to ####.

What am I doing different than you?


Report •

#5
December 1, 2009 at 09:05:41
Cheers for the reply about the time change, I've just checked, and I had already changed the date format.

Thanks all for the help! Looks like my best option for this circumstance is the workaround.


To DerbyDad03:
I'm not sure exactly what the difference is. It must be the 1904 date setting, only thing I can think it would be.

If you follow Mike's advice that should sort it out, I think my custom cell format must only be changing negative numbers to red.


I started setting my spreadsheet up a few months ago, so my memory is a bit hazy!

I hope this helps.


Report •

#6
December 1, 2009 at 09:28:58
Hi,

Here is another way to handle the issue without resorting to the 1904 date system which is typically only used by Mac users.
From Excel Help:
Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel for Windows is 1900. The default date system for Microsoft Excel for the Macintosh is 1904.

You could use two columns, one for positive times and one for negative times. A positive value is entered in either one and the formula handles the value according to whether it is in the positive or negative column.

	B	C	D	E
6	+	-	Base	Final
7	0:20		10:00	10:20
8		0:20	10:00	09:40
The formula in E7 is
=IF(B7<>"",D7+B7,D7-C7)

Regards


Report •


Ask Question