Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 18
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 293
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate the difference between two times - getting '#VALUE!' err Louis de Pointe du Lac Excel Worksheet Functions 2 June 20th 07 10:18 PM
calculate difference between two times Ken Excel Worksheet Functions 6 July 21st 06 09:05 PM
Calculate the difference between two times Buffgirl71 Excel Worksheet Functions 1 February 9th 06 11:10 PM
Calculate the difference two times Chi Excel Discussion (Misc queries) 2 July 16th 05 08:31 PM
How can I calculate the difference between times without using th. KELSEY1022 Excel Worksheet Functions 12 February 2nd 05 06:06 PM


All times are GMT +1. The time now is 12:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"