entering a minus time value into a cell

**nje** · 08-05-2010, 08:26 AM

Confusing title! I've nearly finished (with the help from forumites) my daughters timesheet.
The problem is cell N2. They use flexitime and some weeks they go into negative hours or minutes. This isn't a problem when doing calculations but when they first use this timesheet, they will need to transfer theiir records over from paper. The cell for the flexitime is N2 and this could be for example -3:05. but when I enter the minus sign before the time -3:05 it thinks it is a formula and won't accept it. Any ideas.
Thanks

**Whizbang** · 08-05-2010, 08:39 AM

You can enter it as a decimal value, rather than a time value. 3:05 is the same as 0.128472222222222

So, just enter -0.128472222222222 and confirm and it should be alright.

To get the decimal value, simply enter the time as a positive value. Change the format of the cell to general. Then just throw in a negative symbol in the front and confirm. Then reapply the time format.

**nje** · 08-05-2010, 08:58 AM

Thanks Whizbang ,I've surprised myself by actually following your instructions and gettng it to work. I appreciate your help but, it won't be just my daughters' times being entered and with lots of different times it could get confusing working out the decimal values.

**oldchippy** · 08-05-2010, 09:21 AM

Hi,

You could just use another cell (formatted as General) and refer to N2

**shg** · 08-05-2010, 09:36 AM

You/she can just enter it as =-"3:05" and format as General or Number

**Domski** · 08-05-2010, 09:40 AM

The 1904 date system allows negative times. Just be careful that switching to it part way through creating a worksheet will alter any dates that you already have in the workbook.

Dom

**nje** · 08-05-2010, 10:16 AM

Thanks everyone, still trying but not there yet - I've tried the decimal way, thats a bit longwinded, I've tried entering =-"3:05" and that returns 0,13 in the cell. - Oldchippy, I tried refering another cell and formatting as general but it still required decimal input unless I've misunderstood you.
Domski, thanks I already have the worksheet dated with the 1904 system
The solution:
I've just tried using the time as text 00\:00 which works but then G18 can't translate it as time. So the solution is I think, getting cell G18 to translate the time from N2 (text) into [h]:mm any ideas?

**DonkeyOte** · 08-05-2010, 10:24 AM

If per you sample you're using 1904 date system and enter -"3:05" or =-TIME(3,5,0) the cell should display accordingly if format as Time.

Not quite sure what the problem is - perhaps you could post a further example ?

If you have reverted to 1900 date system then you can not format a negative number as Time without converting to text string.
For ex -0.5 is -12 hours you just can't format as Time - remember that the format is just a mask so you can calculate using -0.5 without issue.

**shg** · 08-05-2010, 10:25 AM

I've tried entering =-"3:05" and that returns 0,13 in the cell.

You can format to show as many decimals as you like; the underlying value is correct.

**nje** · 08-05-2010, 10:44 AM

DonkeyOte, Thank you, you are right, -"3:05" does do it correctly, I think I must have used the wrong format. the reason I was using 00\:00 was because of the simplicity of entering it without having to use the colon, but your method solves the problem. Thank you.