Calculate the difference between two times

[Boyertown Casket]

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.

 

[macropod]

Hi Boyertown Casket,

Provided the total hours does not exceed 24:

=MOD(1+B2-A2,1)

 

[Boyertown Casket]

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.

Hi Boyertown Casket,

Provided the total hours does not exceed 24:

=MOD(1+B2-A2,1)

--
Cheers
macropod
[MVP - Microsoft Word]

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.

 

[macropod]

Hi Boyertown Casket,

Glad to be of assistance.

--
Cheers
macropod
[MVP - Microsoft Word]

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.

Hi Boyertown Casket,

Provided the total hours does not exceed 24:

=MOD(1+B2-A2,1)

--
Cheers
macropod
[MVP - Microsoft Word]

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.