How do I calculate time between 2 dates and exclude weekends and holidays.

All,

How do I calculate time between 2 dates and exclude weekends and holidays.

for example:

 CELL      A2                                  K2

2015/3/27  16:12:32          2015/30/3  08:11:10


I need the time between the 2 cells, excluding weekends and holidays. Also Work hours are 7am - 7pm M-F

Thanks!
LVL 19
Edward PamiasTeam Lead RRS DeskAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
0
Ramachandar NCommented:
Are you somewhere storing the Holiday dates in the excel? Without keeping the holiday dates, you cannot exclude it.
0
Rob HensonFinance AnalystCommented:
Formula from ProfessorJimJam from that previous question, with amendment for holidays:

=(NETWORKDAYS(A2,K2,$M$1:$M$8)-1)*(19/24-7/24)+MOD(K2,1)-MOD(A2,1)

M1:M8 contains list of holidays. (19/24-7/24) allows for 7am to 7pm work shift.

Thanks
Rob H
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Edward PamiasTeam Lead RRS DeskAuthor Commented:
The Holidays are in cells V6 thru V14.
0
Rob HensonFinance AnalystCommented:
For your example, the result will be 3:58:38 (3 hours 58 Minutes and 38 seconds), you can remove the seconds from display by formatting. Use custom format [h]:mm and the hours will show completely, whereas standard hh:mm will only show hours up to 24, so 35 hours would show as 11:00 (24 + 11 = 35 and only 11 shown).

27 March 16:12 to end of shift = 02:48
28 March = Weekend so ignored
29 March = Weekend so ignored
30 March 07:00 to 08:11 = 01:11
02:48 + 01:11 = 03:59

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
Adjusted for holiday range:

=(NETWORKDAYS(A2,K2,$V$6:$V$14)-1)*(19/24-7/24)+MOD(K2,1)-MOD(A2,1)

And to adjust further for dynamic start and finish times:

=(NETWORKDAYS(A2,K2,$V$6:$V$14)-1)*(HOUR($R$2)/24-HOUR($R$1)/24)+MOD(K2,1)-MOD(A2,1)

R1 = 07:00
R2 = 19:00

Thanks
Rob H
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Rob,

With your formula I got 27:58:38   - which is not the answer I was looking for. If the call came in at 16:11( 4:11 PM) on Friday, which leaves 2 hours and 49 minutes left of the work day and we contact the person 8:11 AM on Monday, which is only 1 hour and 11 minutes into the day.  So I would assume I should get 3 hours and 1 minute of time. That is the calculation I am trying to get.
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
Please check the attached file.  
tested and ready to use.
EE.xlsx
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Let me try the updated formula.
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
plz check the attachment i uploaded.

if you would like to explore more on dates.  i recommend you see my answer in http://www.experts-exchange.com/Software/Office_Productivity/Q_28610197.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Mine would give 3:01 as expected
0
Rob HensonFinance AnalystCommented:
Looks like you have missed the -1 as you have 24 hours too high
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
the correct result is given by the file, i have attached previously.

remember that your dates and time has seconds too  4:12:32    8:11:10

so my formula takes into account the seconds as well. therefore saving 2:47:28  +  1:11:10
  = 3:58:38
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Rob's update came in while I was replying back earlier. I just noticed a discrepancy on the sheet. It looks like column C of data may have dates that are newer than column K dates which is giving me a negative number and displaying ######## in some of the cells. The create date (column C) should be older than the contact date (Column K), which in some of the cell is not the case. Any way to make those cells display 00:00:00 instead of ######### ??
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
epamias  

did you see the excel file i uploaded in post ID: 40712852

isn't that answer your question?
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
You and Rob's formula works well but lets say if the create date is 3/30/15 12:00PM and the contactuser time is 3/30/15 11:50AM then I get  ########## in the cell. The create time should be the time before contactuser time.
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
look,  your logic is wrong.  a start time cannot be later than the end time.

i assume your start time would be 12AM nor 12 PM

becuase 12 PM comes later than 11:50 AM

perhaps you are looking for 12:00 AM as the start date not PM
0
Saqib Husain, SyedEngineerCommented:
=if(c2>k2,0,<given_formula>)
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Thanks for all the help guys!
0
barry houdiniCommented:
And to adjust further for dynamic start and finish times:

=(NETWORKDAYS(A2,K2,$V$6:$V$14)-1)*(HOUR($R$2)/24-HOUR($R$1)/24)+MOD(K2,1)-MOD(A2,1)
Hello Rob,

It's simpler to use this version which would also take in to account any minutes and seconds in R1 and R2

=(NETWORKDAYS(A2,K2,$V$6:$V$14)-1)*($R$2-$R$1)+MOD(K2,1)-MOD(A2,1)

Note that for all of the formulas quoted both A2 and K2 need to be within working hours. If it's possible that the start or the end time/dates could be outside working hours (e.g. evenings, weekends or holidays) then you can use this version for correct results:

=(NETWORKDAYS(A2,K2,$V$6:$V$14)-1)*($R$2-$R$1)+IF(NETWORKDAYS(K2,K2,$V$6:$V$14),MEDIAN(MOD(K2,1),R$1,R$2),R$2)-MEDIAN(NETWORKDAYS(A2,A2,$V$6:$V$14)*MOD(A2,1),R$1,R$2)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.