Hello, and thank you in advance for help with this 'formula'. I am trying to
calculate the amount of time between two events. I am able to calculate if
the event times times fall within one calendar day; for example:
Event 1 11:35 AM Event 2 6:42 PM
Amount of Time = (6:42 PM) - (11:35 AM) = 7hr 7min
I am running into a problem when the second event takes place the following
day; for example:
Event 1 10:53 PM Event 2 3:41 AM
Amount of Time = (3:41 AM) - (10:53 PM) = (#VALUE!)
The result should be 4hr 48 min, but that is where I am stuck. Does anyone
know of a formula to perform this calculation when the second event occurs on
the following calendar? Any help will be greatly appreciated.
Thank You,
Dan Wollam

=MOD(A2-A1,1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"WOLLAM" wrote in message
...
Hello, and thank you in advance for help with this 'formula'. I am trying
to
calculate the amount of time between two events. I am able to calculate
if
the event times times fall within one calendar day; for example:
Event 1 11:35 AM Event 2 6:42 PM
Amount of Time = (6:42 PM) - (11:35 AM) = 7hr 7min
I am running into a problem when the second event takes place the
following
day; for example:
Event 1 10:53 PM Event 2 3:41 AM
Amount of Time = (3:41 AM) - (10:53 PM) = (#VALUE!)
The result should be 4hr 48 min, but that is where I am stuck. Does
anyone
know of a formula to perform this calculation when the second event occurs
on
the following calendar? Any help will be greatly appreciated.
Thank You,
Dan Wollam

If it only ever goes into the next day (not 2 or 3 days) then you can use

=IF(endtime<starttime,1+endtime-starttime,endtime-starttime)

(just substitute the cell references for the endtime and starttime cells).

Andrea Jones
www.stratatraining.co.uk
www.wrekinpublishing.com

"WOLLAM" wrote:

Hello, and thank you in advance for help with this 'formula'. I am trying to
calculate the amount of time between two events. I am able to calculate if
the event times times fall within one calendar day; for example:
Event 1 11:35 AM Event 2 6:42 PM
Amount of Time = (6:42 PM) - (11:35 AM) = 7hr 7min
I am running into a problem when the second event takes place the following
day; for example:
Event 1 10:53 PM Event 2 3:41 AM
Amount of Time = (3:41 AM) - (10:53 PM) = (#VALUE!)
The result should be 4hr 48 min, but that is where I am stuck. Does anyone
know of a formula to perform this calculation when the second event occurs on
the following calendar? Any help will be greatly appreciated.
Thank You,
Dan Wollam

Thank you Andrea, I will try your suggestion, as well as Bob's to see which
better suits my needs. Thank you.

"Andrea Jones" wrote:

If it only ever goes into the next day (not 2 or 3 days) then you can use

=IF(endtime<starttime,1+endtime-starttime,endtime-starttime)

(just substitute the cell references for the endtime and starttime cells).

Andrea Jones
www.stratatraining.co.uk
www.wrekinpublishing.com

"WOLLAM" wrote:

Hello, and thank you in advance for help with this 'formula'. I am trying to
calculate the amount of time between two events. I am able to calculate if
the event times times fall within one calendar day; for example:
Event 1 11:35 AM Event 2 6:42 PM
Amount of Time = (6:42 PM) - (11:35 AM) = 7hr 7min
I am running into a problem when the second event takes place the following
day; for example:
Event 1 10:53 PM Event 2 3:41 AM
Amount of Time = (3:41 AM) - (10:53 PM) = (#VALUE!)
The result should be 4hr 48 min, but that is where I am stuck. Does anyone
know of a formula to perform this calculation when the second event occurs on
the following calendar? Any help will be greatly appreciated.
Thank You,
Dan Wollam

Thank you Bob, I will try your suggestion, as well as Andrea's to see which
better suits my needs. Thank you.

"Bob Phillips" wrote:

=MOD(A2-A1,1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"WOLLAM" wrote in message
...
Hello, and thank you in advance for help with this 'formula'. I am trying
to
calculate the amount of time between two events. I am able to calculate
if
the event times times fall within one calendar day; for example:
Event 1 11:35 AM Event 2 6:42 PM
Amount of Time = (6:42 PM) - (11:35 AM) = 7hr 7min
I am running into a problem when the second event takes place the
following
day; for example:
Event 1 10:53 PM Event 2 3:41 AM
Amount of Time = (3:41 AM) - (10:53 PM) = (#VALUE!)
The result should be 4hr 48 min, but that is where I am stuck. Does
anyone
know of a formula to perform this calculation when the second event occurs
on
the following calendar? Any help will be greatly appreciated.
Thank You,
Dan Wollam