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.
Gerald
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?
Barrie
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
Gerald
g.marshall1@tinyworld.co.uk
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
h:mm
3. Enter 5:20 into cell B1
4. Enter =SUM(A1:B1) into cell C1.
