Negativ value of time data
 

Hi!

How can I get to show the negative value of time data in my cells (t.ex.
cell1 - cell2 < 0) and then I've got ###### insted of digits.

W

You can't if you use windows excel standard date system,
you can if you use Mac's 1904 system under toolscalculations

note that you would add 1462 days to any date you previosuly had
so you would need to put 1462 in a cell, copy it and select all dates that
were converted and do editpaste special and check subtract

There are other ways like using conditional formatting and red fonts

=IF(cell1-cell2<0,cell2-cell1,cell1-cell2)

then use formatconditional formatting , formula is and

=cell1-cell2<0

then select format and red fonts

Of course if you are doing calculations it might be a bit tricky if there
are many
negative time values.

Regards,

Peo Sjoblom



"WMA" wrote:

Hi!

How can I get to show the negative value of time data in my cells (t.ex.
cell1 - cell2 < 0) and then I've got ###### insted of digits.

W

You can display it in another cell with:

=IF(B1=A1,TEXT(B1-A1,"[h]:mm:ss"),TEXT(A1-B1,"-
[h]:mm:ss"))

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi!

How can I get to show the negative value of time data in
my cells (t.ex.
cell1 - cell2 < 0) and then I've got ###### insted of
digits.

W
.

Hi

Must the result really be negative, or there is a midnight rollover? I.e
something started at evening, and ended on next morning. When this is the
case, use the formula
=cell1 - cell2 + (cell1<cell2)


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"WMA" wrote in message
...
Hi!

How can I get to show the negative value of time data in my cells (t.ex.
cell1 - cell2 < 0) and then I've got ###### insted of digits.

W

Arvi Laanemets skrev:
Hi

Must the result really be negative, or there is a midnight rollover? I.e
something started at evening, and ended on next morning. When this is the
case, use the formula
=cell1 - cell2 + (cell1<cell2)



Yes, it is negativ, if you work to long f.ex. the company ows you some
time, if you work to short you ow time to company, I've got plenty of
these datas.

W

Peo Sjoblom skrev:
You can't if you use windows excel standard date system,
you can if you use Mac's 1904 system under toolscalculations

note that you would add 1462 days to any date you previosuly had
so you would need to put 1462 in a cell, copy it and select all dates that
were converted and do editpaste special and check subtract

There are other ways like using conditional formatting and red fonts

=IF(cell1-cell2<0,cell2-cell1,cell1-cell2)

then use formatconditional formatting , formula is and

=cell1-cell2<0

then select format and red fonts

Of course if you are doing calculations it might be a bit tricky if there
are many
negative time values.

Regards,

Peo Sjoblom


I wonder why did they change it? Any idea?

W

Hi

Then maybe the number of hours will do.
=(cell1-cell2)*24
and format as number

Arvi Laanemets


"WMA" wrote in message
...
Arvi Laanemets skrev:
Hi

Must the result really be negative, or there is a midnight rollover? I.e
something started at evening, and ended on next morning. When this is
the
case, use the formula
=cell1 - cell2 + (cell1<cell2)



Yes, it is negativ, if you work to long f.ex. the company ows you some
time, if you work to short you ow time to company, I've got plenty of
these datas.

W

Arvi Laanemets skrev:
Hi

Then maybe the number of hours will do.
=(cell1-cell2)*24
and format as number

Arvi Laanemets


I have to proove it

Wacek

Arvi Laanemets skrev:
Hi

Then maybe the number of hours will do.
=(cell1-cell2)*24
and format as number

It works fine :), thanks

Wacek