Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I handle negative time values?
I have a spread sheet that I use for time comparisons on a project. There
are target and actual times that get compared. I want to be able to show the resulting difference as a positive or negative value. For example: Target Time: 9:00 AM Actual Time: 9:01:25 AM I would like to be able to show tha result as + 01:45 I also need to account for the other situation as well: Target Time: 9:00 AM Actual Time: 8:58:25 AM I would like thsi result to show as - 01:35 Any help would be most appreciated. I can definitely get the calaulation to run ok, but I have not yet found a way to format the result in the desired fashion. dknorwood |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I handle negative time values?
On Sun, 8 Jul 2007 18:54:00 -0700, dknorwood
wrote: I have a spread sheet that I use for time comparisons on a project. There are target and actual times that get compared. I want to be able to show the resulting difference as a positive or negative value. For example: Target Time: 9:00 AM Actual Time: 9:01:25 AM I would like to be able to show tha result as + 01:45 I also need to account for the other situation as well: Target Time: 9:00 AM Actual Time: 8:58:25 AM I would like thsi result to show as - 01:35 Any help would be most appreciated. I can definitely get the calaulation to run ok, but I have not yet found a way to format the result in the desired fashion. dknorwood If you can live with it: Tools/Options/Calculations Under Workbook Options SELECT the 1904 Date system. Then format as mm:ss (or perhaps [h]:mm:ss.0) depending on how much precision you wish to display. This will change dates already entered in this workbook by 4 years (and a day), but will allow you to display negative times. Otherwise you would only be able to display the value as a text string -- that would make further mathematical operations difficult. You might be able to get around this issue by using hidden cells for the actual result, and visible cells for the displayed result. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I handle negative time values?
You can use the 1904 date system to display negative time, however, this
will affect any dates you already have entered in your file. They will be off by 4 years (1462 days). This can be a significant drawback! You can fix this side effect but it may not be worth the trouble. ToolsOptionsCalculation1904 date system Then you'd have to subtract 1462 days from the dates that changed. Other options: Use decimal values instead of time formatted values. You can display a negative time as TEXT but this may affect any downstream calculations. -- Biff Microsoft Excel MVP "dknorwood" wrote in message ... I have a spread sheet that I use for time comparisons on a project. There are target and actual times that get compared. I want to be able to show the resulting difference as a positive or negative value. For example: Target Time: 9:00 AM Actual Time: 9:01:25 AM I would like to be able to show tha result as + 01:45 I also need to account for the other situation as well: Target Time: 9:00 AM Actual Time: 8:58:25 AM I would like thsi result to show as - 01:35 Any help would be most appreciated. I can definitely get the calaulation to run ok, but I have not yet found a way to format the result in the desired fashion. dknorwood |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I handle negative time values?
"dknorwood" wrote...
I have a spread sheet that I use for time comparisons on a project. There are target and actual times that get compared. I want to be able to show the resulting difference as a positive or negative value. For example: Target Time: 9:00 AM Actual Time: 9:01:25 AM I would like to be able to show tha result as + 01:45 That should be + 1:25. I also need to account for the other situation as well: Target Time: 9:00 AM Actual Time: 8:58:25 AM I would like thsi result to show as - 01:35 .... If you don't want to change your date base from 1900 to 1904, you could achieve this using text formulas, specifically, =IF(TargetTime<=ActualTime,"+ ","- ") &TEXT(MAX(TargetTime,ActualTime)-MIN(TargetTime,ActualTime),"[m]:ss") If you then need to sum these time difference values, which I'll assume are in a range named Delta, and display the result in the same format, try =IF(SUMPRODUCT(2-FIND(LEFT(Delta,1),"- +"),-MID(Delta,3,12))0,"+ ","- ") &TEXT(ABS(SUMPRODUCT(2-FIND(LEFT(Delta,1),"- +"),-MID(Delta,3,12))),"[h]:mm") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I display negative time values in excell? | Excel Worksheet Functions | |||
entering negative time values in excel | Excel Discussion (Misc queries) | |||
Get Excel to accept negative values using time-format | Excel Discussion (Misc queries) | |||
how do i sum up a column of time values but ignore negative values | New Users to Excel | |||
Negative Time Values | Excel Discussion (Misc queries) |