How do I display negative time?

[Guest]

I calculate the amount of time spent a day and need to compare that with a
constant (7hrs). I need to display how much time in minutes under the 7hrs.
Currently it shows ######. The calculation is correct but I can't display the
actual figure.

 

[Guest]

moman,

Time will not allow negative values it displays ###### as you have found out.

However assuming that the result of your time spent per day calculations are
in C4, then the following formula in E4 will give you what you want:

=IF((C4/24)-7/24<0,CONCATENATE("-
",TEXT((7/24-(C4/24)),"[HH]:mm:ss")),TEXT(((C4/24)-7/24),"[HH]:mm:ss"))

E.G.

Col A Col B Col C Col D Col E


Hours Spent Diff from 7 Hours
5.5 - 01:30:00
9.0 02:00:00

Etc.

You could then get fancy and use conditional formatting to make any entry in
Col E that starts with a minus sign display as red text.

unlikeKansas

 

[Andy Brown]

Time will allow negative values if you turn on "1904 date system" (Tools --
Options -- Calculation). However, this will affect dates that already exist
in the workbook (if any) and therefore may not be suitable.

Rgds,
Andy

 

[dcronje]

Do you require the answer in time format?

Why not say:

=(7hours-hours_used)*60

so for example:

=(((B2-A2)*24)-$C$1)*60

C1 being the constant 7 hours for example
A2 start time
B2 end time

the answer will be in minutes used.

You may be able to adapt the above for your purposes

 

[Gord Dibben]

Negative times are shown as ################ in the cell. The underlying
value is there and can be used in calculations.

You can change this to show a negative serial number, but not a formatted time
such as 10:15

Format>Cells>Custom. Pick any format then delete everything in the dialog
box>OK

If you switch to 1904 date system in Tools>Options>Calculation you can show
negative times, but this may cause other problems.

For more on date/time arithmetic see Chip Pearson's site at

http://www.cpearson.com/excel/datetime.htm#SerialDates

Gord Dibben Excel MVP