# formula to calculate time difference crossing midnight

1. ## formula to calculate time difference crossing midnight

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?  Register To Reply

2. ## Re: formula to calculate time difference crossing midnight

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
__________________________  Register To Reply

3. ## RE: formula to calculate time difference crossing midnight

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?  Register To Reply

4. ## RE: formula to calculate time difference crossing midnight

=(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?  Register To Reply

5. ## Re: formula to calculate time difference crossing midnight

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.  Register To Reply

6. ## Re: formula to calculate time difference crossing midnight Thank you I was dealing with the same situation, and this formula seems to work perfectly.   Register To Reply

7. ## Re: formula to calculate time difference crossing midnight

One way way...

A1 = 8:00 PM
B1 = 3:00 AM

=MOD(B1-A1,1)

Format as h:mm  Register To Reply

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1