In Excel I have been trying to find an easier way to calculate a time
difference where the times cross midnight. Example:
Start time: 23:50:00
End time: 00:15:00
How would you formulate an equation to determine the duration of time or
differnce between the start and end time?
On Thu, 17 Aug 2006 07:16:03 -0700, ditorejax
<ditorejax@discussions.microsoft.com> wrote:
>In Excel I have been trying to find an easier way to calculate a time
>difference where the times cross midnight. Example:
>Start time: 23:50:00
>End time: 00:15:00
>
>How would you formulate an equation to determine the duration of time or
>differnce between the start and end time?
One way which results in hours and decimal of an hour is
=IF((end-start)<0,(end-start)*24+24,(end-start)*24)
If you want to see hours and minutes you'd need to modify it to pick
up the decimal fraction and multiply it by 60.
HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
Hi,
one way is add 1 in 00:15:00
assuming that 23:50:00 is on A2 and 00:15:00 is on A3
=a3+1-a2
=00:25:00
hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo
"ditorejax" escreveu:
> In Excel I have been trying to find an easier way to calculate a time
> difference where the times cross midnight. Example:
> Start time: 23:50:00
> End time: 00:15:00
>
> How would you formulate an equation to determine the duration of time or
> differnce between the start and end time?
=(A2>A3)+A3-A2
1 only needs to be added if A2>A3 otherwise you get 24 extra hours when
times don't go over midnight. You will see the problem when you start
summing the times and if you format as [h]:mm:ss
"Marcelo" wrote:
> Hi,
>
> one way is add 1 in 00:15:00
>
> assuming that 23:50:00 is on A2 and 00:15:00 is on A3
> =a3+1-a2
>
> =00:25:00
>
> hth
> --
> regards from Brazil
> Thanks in advance for your feedback.
> Marcelo
>
>
>
> "ditorejax" escreveu:
>
> > In Excel I have been trying to find an easier way to calculate a time
> > difference where the times cross midnight. Example:
> > Start time: 23:50:00
> > End time: 00:15:00
> >
> > How would you formulate an equation to determine the duration of time or
> > differnce between the start and end time?
Thanks for all the help! I've been calculating these crossing midnight durations manually to-date and it's laborious and error-prone.
To recap and combine the advice, the below formula can take care of a duration calculation both for cases where a duration crosses midnight and where it does not cross midnight. The specific row #s and column #s would need to be modified to suit your data. The extra parentheses when subtracting the begin time from the end time +1 may not be needed, but it makes the formula clearer to me.
- Formula using the convention other posters have used where the end time is in the row below the begin time:
=IF(A3<A2,(A3+1)-(A2),(A3-A2))
- Formula using a different convention where column A contains the begin time and column B contains the end time. This formula suits my data:
=IF(B2<A2,(B2+1)-(A2),(B2-A2))
Note, however, that a different formula or manual efforts may be needed if you are crossing over more than one day. In that case, you might need to combine/concatenate the day and time together, but I don't have this condition in my data so I have not tried to account for it. Just a heads-up for those that have this condition.
Last edited by Indiver716; 01-24-2013 at 11:02 AM. Reason: Did not originally follow the original poster's column and row convention.
Thank you I was dealing with the same situation, and this formula seems to work perfectly.
One way way...
A1 = 8:00 PM
B1 = 3:00 AM
=MOD(B1-A1,1)
Format as h:mm
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks