• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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!
0
Edward Pamias
Asked:
Edward Pamias
  • 6
  • 6
  • 5
  • +3
2 Solutions
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
 
ProfessorJimJamCommented:
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
 
ProfessorJimJamCommented:
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
 
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
 
ProfessorJimJamCommented:
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
 
ProfessorJimJamCommented:
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
 
ProfessorJimJamCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 6
  • 6
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now