Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate saved or lost time
Have defined the cells customized as time h:mm
Let us say that a specified job should take "C19" = 1:20 (h:mm) Now it takes for someone "B19" = 1:18 (h:mm) Means minus 2 minutes better than standard. I write C20=C19-B19 get value not correct If time is higher than std time then value looks good. How should I write the formula? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate saved or lost time
"not correct" isn't enough information to troubleshoot your problem. Is
there some reason you couldn't identify the result that you got? The formula is correct. Check your formatting or ensure the values you have are times, not text. If you're tying to display a negative time, Excel does not support that unless you switch to the 1904 date system. Regards, Fred. "MSSailor" wrote in message ... Have defined the cells customized as time h:mm Let us say that a specified job should take "C19" = 1:20 (h:mm) Now it takes for someone "B19" = 1:18 (h:mm) Means minus 2 minutes better than standard. I write C20=C19-B19 get value not correct If time is higher than std time then value looks good. How should I write the formula? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate saved or lost time
"MSSailor" wrote:
Let us say that a specified job should take "C19" = 1:20 (h:mm) Now it takes for someone "B19" = 1:18 (h:mm) Means minus 2 minutes better than standard. I write C20=C19-B19 get value not correct First, that will result in positive 2 minutes. If you want -2 minutes, you should compute =B19-C19. But that will result in "###" unless you format the cell as Number. You cannot display negative time in the h:mm format. If you want the difference to be displayed as integer minutes, not h:mm whether or not it is negative, try: =round((B19-C19)*1440,0) formatted as General or Number. Explanation: Time (h:mm) is stored as a fraction of a day. So the number of minutes is, ostensibly, value times hours-per-day times minutes-per-hour (24*60 = 1440). You need ROUND because sometimes the arithmetic does not result in an exact integer due to the way that Excel (like most applications) represents decimal fractions. For example, with your numbers, B19-C19 is displayed as -1.9999999999999 when formatted to 15 significant digits, which Excel treats as different from 2. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate saved or lost time
I did as you said and got "#NAME?"
" wrote: "MSSailor" wrote: Let us say that a specified job should take "C19" = 1:20 (h:mm) Now it takes for someone "B19" = 1:18 (h:mm) Means minus 2 minutes better than standard. I write C20=C19-B19 get value not correct First, that will result in positive 2 minutes. If you want -2 minutes, you should compute =B19-C19. But that will result in "###" unless you format the cell as Number. You cannot display negative time in the h:mm format. If you want the difference to be displayed as integer minutes, not h:mm whether or not it is negative, try: =round((B19-C19)*1440,0) formatted as General or Number. Explanation: Time (h:mm) is stored as a fraction of a day. So the number of minutes is, ostensibly, value times hours-per-day times minutes-per-hour (24*60 = 1440). You need ROUND because sometimes the arithmetic does not result in an exact integer due to the way that Excel (like most applications) represents decimal fractions. For example, with your numbers, B19-C19 is displayed as -1.9999999999999 when formatted to 15 significant digits, which Excel treats as different from 2. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I lost a Spreadsheet : Can I search by the DATE I saved it? | Excel Discussion (Misc queries) | |||
Lost my list box icon when saved as template. | Excel Worksheet Functions | |||
lost work that wasn't saved. Can I retrieve it? How? | Excel Discussion (Misc queries) | |||
adding a code to calculate how much time is lost | Excel Discussion (Misc queries) | |||
recovering a lost book in excel that was not saved... | Excel Discussion (Misc queries) |