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

# 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
• 6
• 6
• 5
• +3
2 Solutions

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

Commented:
EE.xlsx
0

Let me try the updated formula.
0

Commented:
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

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

Commented:
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

Commented:
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

Commented:
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
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.