entering negative time values in excel

[dona]

I need to enter a negative time value in excel but can't work out how to do
it. Can anyone help?

Thanks

 

[Dave Peterson]

First, you'll want to change to the 1904 date system.
Tools|options|Calculation tab

Then you can enter the negative time like:

=-TIME(1,30,0)
for -1:30 AM

You could also use:
=-"01:30:00"
(but format the cell as time)

Be aware that if you have dates in your workbook, they'll change by 4 years and
a day.

 

[dona]

Thanks for that, unfortunately I don't think that will help in my particular
circumstances.

This is for a time sheet that will be used by lots of people, most of whom
will be Excel novices. They will need to be able to enter, for example
-01:00, if they have worked 1 hour less than their conditioned hours.

I think I need some way of formatting the cell that that either positive or
negative time values can be entered.

Unfortunatley it is also not a cell that could be populated with a formula.

Any suggestions?

 

[Dave Peterson]

I don't have any other suggestions.

Thanks for that, unfortunately I don't think that will help in my particular
circumstances.

This is for a time sheet that will be used by lots of people, most of whom
will be Excel novices. They will need to be able to enter, for example
-01:00, if they have worked 1 hour less than their conditioned hours.

I think I need some way of formatting the cell that that either positive or
negative time values can be entered.

Unfortunatley it is also not a cell that could be populated with a formula.

Any suggestions?

 

[Dave Peterson]

Use multiple cells?

One for the time and one for +/- indicator and then one that combines the two?

 

[dona]

Thanks, I will try that.

Use multiple cells?

One for the time and one for +/- indicator and then one that combines the two?