Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculate the difference between two times
I understand and have utilized with success the subtraction operator to find
the difference in times when it is within the same 24 hour period. My problem arises when the ENDING time is outside the 24 hour period. For instance, I start a job at 2015 HRS and complete it at 0015 HRS. the next day. When you utilize the normal subtraction operator, in this case, it returns '#####' indicating negative numbers. I can do a work-around by adding 24 to the ending time - for example, normal subtraction operator would be: =B2-A2 now when I am outside the 24 hour period, I can calculate it this way: =(B2+24)-A2 . This works; however, I want to set this up so that I do not have to worry about whether the ending time is outside the 24 hour period. Any suggestions on how to write the formula? Thanks. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculate the difference between two times
MACROPOD,
I sincerely appreciate your response to my problem. Your solution worked and I applied it to all applicable cells as I would never know which one would result in the dreaded '####'. :) After I typed in the formula and hit ENTER, the result made me feel like a child with his first Christmas present. "Wow! Kewl!! Exactly what I wanted!!!" Although it may seem like a simple exercise to you, it was hell for me as I have never encountered something like this requiring me to utilize anything outside of a simple subtraction operator. :) Again, thank you very much for your excellent advice and for not making me feel stupid. :) "macropod" wrote: Hi Boyertown Casket, Provided the total hours does not exceed 24: =MOD(1+B2-A2,1) -- Cheers macropod [MVP - Microsoft Word] "Boyertown Casket" <Boyertown wrote in message ... I understand and have utilized with success the subtraction operator to find the difference in times when it is within the same 24 hour period. My problem arises when the ENDING time is outside the 24 hour period. For instance, I start a job at 2015 HRS and complete it at 0015 HRS. the next day. When you utilize the normal subtraction operator, in this case, it returns '#####' indicating negative numbers. I can do a work-around by adding 24 to the ending time - for example, normal subtraction operator would be: =B2-A2 now when I am outside the 24 hour period, I can calculate it this way: =(B2+24)-A2 . This works; however, I want to set this up so that I do not have to worry about whether the ending time is outside the 24 hour period. Any suggestions on how to write the formula? Thanks. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculate the difference between two times
Hi Boyertown Casket,
Glad to be of assistance. -- Cheers macropod [MVP - Microsoft Word] "Boyertown Casket" wrote in message ... MACROPOD, I sincerely appreciate your response to my problem. Your solution worked and I applied it to all applicable cells as I would never know which one would result in the dreaded '####'. :) After I typed in the formula and hit ENTER, the result made me feel like a child with his first Christmas present. "Wow! Kewl!! Exactly what I wanted!!!" Although it may seem like a simple exercise to you, it was hell for me as I have never encountered something like this requiring me to utilize anything outside of a simple subtraction operator. :) Again, thank you very much for your excellent advice and for not making me feel stupid. :) "macropod" wrote: Hi Boyertown Casket, Provided the total hours does not exceed 24: =MOD(1+B2-A2,1) -- Cheers macropod [MVP - Microsoft Word] "Boyertown Casket" <Boyertown wrote in message ... I understand and have utilized with success the subtraction operator to find the difference in times when it is within the same 24 hour period. My problem arises when the ENDING time is outside the 24 hour period. For instance, I start a job at 2015 HRS and complete it at 0015 HRS. the next day. When you utilize the normal subtraction operator, in this case, it returns '#####' indicating negative numbers. I can do a work-around by adding 24 to the ending time - for example, normal subtraction operator would be: =B2-A2 now when I am outside the 24 hour period, I can calculate it this way: =(B2+24)-A2 . This works; however, I want to set this up so that I do not have to worry about whether the ending time is outside the 24 hour period. Any suggestions on how to write the formula? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate the difference between two times - getting '#VALUE!' err | Excel Worksheet Functions | |||
calculate difference between two times | Excel Worksheet Functions | |||
Calculate the difference between two times | Excel Worksheet Functions | |||
Calculate the difference two times | Excel Discussion (Misc queries) | |||
How can I calculate the difference between times without using th. | Excel Worksheet Functions |