# 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
###### Who is Participating?

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.

Finance AnalystCommented:
0
Commented:
Are you somewhere storing the Holiday dates in the excel? Without keeping the holiday dates, you cannot exclude it.
0
Finance 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
The Holidays are in cells V6 thru V14.
0
Finance 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
Finance AnalystCommented:

=(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
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
Microsoft Excel ExpertCommented:
EE.xlsx
0
Let me try the updated formula.
0
Microsoft 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

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

Finance AnalystCommented:
Mine would give 3:01 as expected
0
Finance AnalystCommented:
Looks like you have missed the -1 as you have 24 hours too high
0
Microsoft 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
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
Microsoft Excel ExpertCommented:
epamias

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

0
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
Microsoft 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
EngineerCommented:
=if(c2>k2,0,<given_formula>)
0
Thanks for all the help guys!
0
Commented:
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.