Negative time calculations
I'm missing something obvious but I can't work out what.
I'm trying to calculate the difference between two time formatted cells (hh:mm:ss) where x - y is likely to result in a negative time (for example -07:20:00). Excel displays the negative time as ###. If I change the formula cell to a general format (rather than a time format) then the number is displayed but obviously the time formatting is lost (3 and a half hours appears as 3.5). Is there a simple way to force Excel to display negative times? |
Negative time calculations
On Sat, 10 Nov 2007 18:46:27 -0000, "Gareth" wrote:
I'm missing something obvious but I can't work out what. I'm trying to calculate the difference between two time formatted cells (hh:mm:ss) where x - y is likely to result in a negative time (for example -07:20:00). Excel displays the negative time as ###. If I change the formula cell to a general format (rather than a time format) then the number is displayed but obviously the time formatting is lost (3 and a half hours appears as 3.5). Is there a simple way to force Excel to display negative times? Tools/Options/Calculation Workbook Options Select 1904 Date system Be advised that this will throw off all of your other dates by four years + 1 day. --ron |
Negative time calculations
Tools Optitions Calculation tab select 1904 date system OK out
"Gareth" wrote: I'm missing something obvious but I can't work out what. I'm trying to calculate the difference between two time formatted cells (hh:mm:ss) where x - y is likely to result in a negative time (for example -07:20:00). Excel displays the negative time as ###. If I change the formula cell to a general format (rather than a time format) then the number is displayed but obviously the time formatting is lost (3 and a half hours appears as 3.5). Is there a simple way to force Excel to display negative times? |
Negative time calculations
Simple but possibly disruptive if this workbook is linked to others.
Use the 1904 date system on that workbook. See John's Walkenbach's site for more. http://www.j-walk.com/ss/excel/usertips/tip051.htm Gord Dibben MS Excel MVP On Sat, 10 Nov 2007 18:46:27 -0000, "Gareth" wrote: I'm missing something obvious but I can't work out what. I'm trying to calculate the difference between two time formatted cells (hh:mm:ss) where x - y is likely to result in a negative time (for example -07:20:00). Excel displays the negative time as ###. If I change the formula cell to a general format (rather than a time format) then the number is displayed but obviously the time formatting is lost (3 and a half hours appears as 3.5). Is there a simple way to force Excel to display negative times? |
Negative time calculations
Hello Gareth,
Can you explain a little more what you're doing, are these times of day or time durations, why do you need to get negative amounts? If you just want to display like a negative you could use the following formula =IF(A1<B1,"-","")&TEXT(ABS(A1-B1),"hh:mm:ss") This returns a text string so it's not recommended if you need to do further calculations with the result "Gord Dibben" wrote: Simple but possibly disruptive if this workbook is linked to others. Use the 1904 date system on that workbook. See John's Walkenbach's site for more. http://www.j-walk.com/ss/excel/usertips/tip051.htm Gord Dibben MS Excel MVP On Sat, 10 Nov 2007 18:46:27 -0000, "Gareth" wrote: I'm missing something obvious but I can't work out what. I'm trying to calculate the difference between two time formatted cells (hh:mm:ss) where x - y is likely to result in a negative time (for example -07:20:00). Excel displays the negative time as ###. If I change the formula cell to a general format (rather than a time format) then the number is displayed but obviously the time formatting is lost (3 and a half hours appears as 3.5). Is there a simple way to force Excel to display negative times? |
All times are GMT +1. The time now is 10:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com