Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula for negative time
Jose,
Excel does not have a concept of negative time (or date for that matter) and will produce an error. VBA can work with negative time though, so may be UDF will work for you. Depends on your aim. NickHK "Jose G. Jasso" <Jose G. wrote in message ... trying to see if a formula in excel can calculate negative time. Example after 40 hours would be displayed as negative time. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula for negative time
"NickHK" wrote: Jose, Excel does not have a concept of negative time (or date for that matter) and will produce an error. VBA can work with negative time though, so may be UDF will work for you. Depends on your aim. NickHK "Jose G. Jasso" <Jose G. wrote in message ... trying to see if a formula in excel can calculate negative time. Example after 40 hours would be displayed as negative time. Thank you for the response. your advice does help, but I'm bent on trying to get this to work in excel somehow. if you have any other advice please reply. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula for negative time
Jose,
Depends what you mean by "negative time" then. You can display a number like -24, that indicates a deduction of 24 hours from another number, but Excel cannot show this formatted as a Time (i.e. with a value of -1). NickHK "Jose G. Jasso" wrote in message ... "NickHK" wrote: Jose, Excel does not have a concept of negative time (or date for that matter) and will produce an error. VBA can work with negative time though, so may be UDF will work for you. Depends on your aim. NickHK "Jose G. Jasso" <Jose G. wrote in message ... trying to see if a formula in excel can calculate negative time. Example after 40 hours would be displayed as negative time. Thank you for the response. your advice does help, but I'm bent on trying to get this to work in excel somehow. if you have any other advice please reply. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula for negative time
"NickHK" wrote in message
Excel does not have a concept of negative time Actually it does, sort of, at least for elapsed times. Times of day (clock time) must be treated differently. You can do any sort of calculations with negative times. The only restriction is that Excel can't display negative time unless you are using the 1904 Date System. Enable the 1904 Date System, and enter the following formula: =TIME(9,0,0)-TIME(12,0,0) Set the format to "hh:mm" and you'll get "-03:00" in the cell. This is negative 3 hours, *not* 9PM the previous day. The actual value of the cell is -0.125, or negative 1/8 of a day. This is used for elapsed time, not time of day. 9 hours minus 12 hours is -3 hours, not 9PM the previous day. To get time of day, you still need to add 1 if the result is negative: =D3-D4+(D3<D4) where D3 is =TIME(9,0,0) and D4 is =TIME(12,0,0) This returns the expected 21:00:00, or 9PM, but then you're into positive times. Not terribly intuitive, but it does work. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "NickHK" wrote in message ... Jose, Excel does not have a concept of negative time (or date for that matter) and will produce an error. VBA can work with negative time though, so may be UDF will work for you. Depends on your aim. NickHK "Jose G. Jasso" <Jose G. wrote in message ... trying to see if a formula in excel can calculate negative time. Example after 40 hours would be displayed as negative time. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula for negative time
Chip,
I did have some memory of possibilities, so thanks for the clarification. Depending on what the OP is actually looking for, this may be an option. Although to me "-3.00 AM" does not have much meaning if it does not relate to a clock time. Out if curiousity, why do the 2 date systems work differently ? Something to do Lotus/Mac compatibility ? NickHK "Chip Pearson" wrote in message ... "NickHK" wrote in message Excel does not have a concept of negative time Actually it does, sort of, at least for elapsed times. Times of day (clock time) must be treated differently. You can do any sort of calculations with negative times. The only restriction is that Excel can't display negative time unless you are using the 1904 Date System. Enable the 1904 Date System, and enter the following formula: =TIME(9,0,0)-TIME(12,0,0) Set the format to "hh:mm" and you'll get "-03:00" in the cell. This is negative 3 hours, *not* 9PM the previous day. The actual value of the cell is -0.125, or negative 1/8 of a day. This is used for elapsed time, not time of day. 9 hours minus 12 hours is -3 hours, not 9PM the previous day. To get time of day, you still need to add 1 if the result is negative: =D3-D4+(D3<D4) where D3 is =TIME(9,0,0) and D4 is =TIME(12,0,0) This returns the expected 21:00:00, or 9PM, but then you're into positive times. Not terribly intuitive, but it does work. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "NickHK" wrote in message ... Jose, Excel does not have a concept of negative time (or date for that matter) and will produce an error. VBA can work with negative time though, so may be UDF will work for you. Depends on your aim. NickHK "Jose G. Jasso" <Jose G. wrote in message ... trying to see if a formula in excel can calculate negative time. Example after 40 hours would be displayed as negative time. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula for negative time
Something to do Lotus/Mac compatibility ?
Actually for compatibility with DOS. Excel was originally written for the Mac. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "NickHK" wrote in message ... Chip, I did have some memory of possibilities, so thanks for the clarification. Depending on what the OP is actually looking for, this may be an option. Although to me "-3.00 AM" does not have much meaning if it does not relate to a clock time. Out if curiousity, why do the 2 date systems work differently ? Something to do Lotus/Mac compatibility ? NickHK "Chip Pearson" wrote in message ... "NickHK" wrote in message Excel does not have a concept of negative time Actually it does, sort of, at least for elapsed times. Times of day (clock time) must be treated differently. You can do any sort of calculations with negative times. The only restriction is that Excel can't display negative time unless you are using the 1904 Date System. Enable the 1904 Date System, and enter the following formula: =TIME(9,0,0)-TIME(12,0,0) Set the format to "hh:mm" and you'll get "-03:00" in the cell. This is negative 3 hours, *not* 9PM the previous day. The actual value of the cell is -0.125, or negative 1/8 of a day. This is used for elapsed time, not time of day. 9 hours minus 12 hours is -3 hours, not 9PM the previous day. To get time of day, you still need to add 1 if the result is negative: =D3-D4+(D3<D4) where D3 is =TIME(9,0,0) and D4 is =TIME(12,0,0) This returns the expected 21:00:00, or 9PM, but then you're into positive times. Not terribly intuitive, but it does work. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "NickHK" wrote in message ... Jose, Excel does not have a concept of negative time (or date for that matter) and will produce an error. VBA can work with negative time though, so may be UDF will work for you. Depends on your aim. NickHK "Jose G. Jasso" <Jose G. wrote in message ... trying to see if a formula in excel can calculate negative time. Example after 40 hours would be displayed as negative time. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula for negative time
??
The 2 date systems are there for lotus compatability / original Excel/Mac but why does the different time display have anything to do with dos Surely if Excel was written for Mac it used the 1904 system Excel for Windows changed to 1900 for compatability with Lotus Where does Dos come into it ? Not challenging your point, just interested. Steve On Wed, 06 Dec 2006 06:19:20 -0000, Chip Pearson wrote: Something to do Lotus/Mac compatibility ? Actually for compatibility with DOS. Excel was originally written for the Mac. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hi, Harlan Grove, ? about negative time formula | Excel Worksheet Functions | |||
formula for negative time durations | Excel Worksheet Functions | |||
Calculating time and negative time | Excel Worksheet Functions | |||
What is the formula to display a negative time results? | Excel Worksheet Functions | |||
Negative time should be allowed in Excel, eg time difference | Excel Discussion (Misc queries) |