MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Problems entering -7:20 in cell

Posted by Gerald Marshall on November 16, 2001 1:24 PM

This is probably a very stupid question but I can not find a way round it.

In cell A1 I want to write -7:20 (As in time, with a colon separating the hours from the minutes). In Cell B1 I want to enter 5:20, I want to end up with -2:00 in cell C1. How do I do it?
My problem is that when I enter -7:20 in cell A1 it assumes I am entering a formula and will not let me enter it.
Any help greatly appreciated.


Posted by Barrie Davidson on November 16, 2001 1:32 PM

Gerald, you can't (at least I don't think so) enter a negative number. Why not try this

Custom format cell A1 as "-hh:mm" (take out the quotation marks). Enter 7:20 in cell A1, 5:20 in cell B1, and "=B1-A1" in cell C1. Note, you will have to change your date settings to "1904 date system" (through Tools|Options) to show the result in cell C1.

Does this help you out?


Posted by Gerald Marshall on November 16, 2001 1:57 PM

Thanks very much Barrie.
That works O.K but I still can not understand how a cell will return a minus time using a formula but will not accept a minus time when entered directly into a cell.
By the way your web site looks good over here in Northern Ireland, I have entered it into my favourites, I expect I will be visiting it quite often for help.

Thanks again

Posted by Mark w. on November 16, 2001 3:40 PM

Here's how...

1. Change your worksheet's date system to 1904
by choosing the Tools | Options... menu command,
clicking on the Calculation tab and checking
"1904 date system"
2. Enter =-"7:20" into cell A1 and format as
3. Enter 5:20 into cell B1
4. Enter =SUM(A1:B1) into cell C1.