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.

Calculate the difference between two times
 

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.

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.

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.