Negativ value of time data

[WMA]

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

 

[Peo Sjoblom]

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

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 edit>paste 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 format>conditional 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

 

[Jason Morin]

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

 

[Arvi Laanemets]

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)

 

[WMA]

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

 

[WMA]

Peo Sjoblom skrev:

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

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 edit>paste 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 format>conditional 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

 

[Arvi Laanemets]

Hi

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

Arvi Laanemets

 

[WMA]

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

 

[WMA]

Arvi Laanemets skrev:

Hi

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

It works fine , thanks

Wacek