Time Difference Between 2 Dates

**JEFLilly** · 03-06-2013, 12:26 AM

Greetings, I am new to the excel forum so please forgive any breach of protocal

I am trying to write a formula to determine the number of working hours and minutes between two dates (orders accepted and orders completed). I only want to consider working hours (6:15-3:15) and exclude weekends and holidays.

Example;
Accepted 9/20/2012 3:36 PM
Completed 9/24/2012 7:07 AM

This order was accepted after the close of business for us so the answer should be 9.867 hours (9 hours for 9/21 and .867 hours for 9/24).

I found a formula from a thread a couple of years ago, but I must be doing something wrong in applying it to my situation
=(17-HOUR(A2)+(MINUTE(A2)/60))+((NETWORKDAYS(A2,B2,"3/7/2011")-2)*8.5) + (HOUR(B2)+(MINUTE(B2)/60) - 8.5)

If some can help it would be much appreciated. I have over 30,000 rows of data to make this calculation for

**:) Sixthsense :)** · 03-06-2013, 01:02 AM

This is not the complete solution for your query, just an idea

In A1 cell
9/20/2012 3:36 PM

In B1 cell
9/24/2012 7:07 AM

In C1 cell
=TEXT(B1-A1,"[h]:mm:ss")

The above formula will get the total number of hours between two date's and after that you calculate the number of holidays and the based on that get the number of non working hours and subtract it from the total working hours

**daddylonglegs** · 03-08-2013, 12:29 PM

TRy this formula

=24*((NETWORKDAYS(A2,B2)-1)*("15:15"-"6:15")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"6:15","15:15"),"15:15")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"6:15","15:15"))

format result cell as number

If you want to exclude holidays then you need to add the holiday range to each of the 3 NETWORKDAYS functions

Time Difference Between 2 Dates

LinkBack

Thread Tools

Rate This Thread

Display

Time Difference Between 2 Dates

Re: Time Difference Between 2 Dates

Re: Time Difference Between 2 Dates

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions