Time / Negative Time

[Kiaat]

Hi,

If, for instance, in Excel you write 17:00 - 16:30, it will produc
0:30. However, 16:30 - 17:00 will produce #####, understandibly
because you cannot have - ? o'clock. By formatting this to a number
can produce -0.5.

What if I still want to represent the decimal, but out of 60, so that
can still get -0.30. I have tried messing about with fractions and
still can't get it. Any ideas?

Thanks,

 

[Ron Rosenfeld]

Hi,

If, for instance, in Excel you write 17:00 - 16:30, it will produce
0:30. However, 16:30 - 17:00 will produce #####, understandibly,
because you cannot have - ? o'clock. By formatting this to a number I
can produce -0.5.

What if I still want to represent the decimal, but out of 60, so that I
can still get -0.30. I have tried messing about with fractions and I
still can't get it. Any ideas?

Thanks,

K

1. Format as time and change to the 1904 date system. That will give you
-0:30

Tools/Options/Calculations Workbook Options Select 1904 Date System

2. Use this formula:

=(A2-A1)*24*60/100 or =(A2-A1)*14.4 and format as General



--ron

 

[Dave Peterson]

If you change:

Tools|options|calculation tab
and enable "1904 date system"

You can see negative times--but watch your dates. You'll be off by 4 years and
one day.

Or you could see it as text:

=IF(A1<B1,"-","")&TEXT(ABS(B1-A1),"hh:mm")