Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
Arvi Laanemets skrev:
Hi Then maybe the number of hours will do. =(cell1-cell2)*24 and format as number It works fine :), thanks Wacek |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing Time Data | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Split combined date time data | Excel Discussion (Misc queries) | |||
Split combined date time data | Excel Discussion (Misc queries) | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |