Calculate Working Days in MS Excel

September 17, 2018 at 06:06:31
Specs: Windows 7
Hi,

Can you please help?

I'm trying to calculate the number of working days from a given start date in Excel.
This should exclude weekends and bank holidays.


For e.g. my start date is 07/09/2018


Thanks in advance.


See More: Calculate Working Days in MS Excel

Reply ↓  Report •

#1
September 17, 2018 at 07:44:33
One oif these should be what you are looking for:

NETWORKDAYS function:

https://support.office.com/en-us/ar...

WORKDAY function:

https://support.office.com/en-us/ar...

Which one you use will depend on what your exact needs are.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
September 17, 2018 at 07:52:09
One thing to be aware of when using the NETWORKDAYS() function
your answer will include BOTH the Start Date and End Date

So if your start date is 07/09/2018
and your end date is 07/11/2018
your answer will be 3 days

This is can sometimes be confusing.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
September 17, 2018 at 09:34:01
Confusing? Sure is.

If the answer includes both the Start Date and End Date, please explain the following results. (Not arguing, just not understanding)

If 7/9 is a weekend date, why is it counted in the first place? Weekends are supposed to be excluded, period.

Why is the answer the same (5) for a Start Date of 7/9 and End Dates of 7/13, 7/14 and 7/15?

I agree with 5 for 7/14 and 7/15 since Monday-Friday is 7/10-7/14, so that is 5 workdays. If both the Start Date and End Date are included, why aren't the results for those showing up as 6 and 7?

That "rule" seems to fits for a Start Date of 7/9 and End Dates of 7/11 - 7/13, but seems to fall apart after that.

I am definitely confused.

 
       A               B           C
1    7/9/2018	    7/11/2018	   3
2    7/9/2018	    7/12/2018	   4
3    7/9/2018	    7/13/2018	   5
4    7/9/2018	    7/14/2018      5 
5    7/9/2018	    7/15/2018	   5  
6    7/9/2018	    7/16/2018	   6
7    7/9/2018	    7/17/2018	   7

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
September 17, 2018 at 14:33:49
NETWORKDAYS() - Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays

Adding the Days of the Week makes it clearer:

Monday, July 9, 2018	Tuesday, July 10, 2018	     2
Monday, July 9, 2018	Wednesday, July 11, 2018     3
Monday, July 9, 2018	Thursday, July 12, 2018	     4
Monday, July 9, 2018	Friday, July 13, 2018	     5
Monday, July 9, 2018	Saturday, July 14, 2018	     5
Monday, July 9, 2018	Sunday, July 15, 2018	     5
Monday, July 9, 2018	Monday, July 16, 2018	     6
Monday, July 9, 2018	Tuesday, July 17, 2018	     7

The 14th & 15Th are Weekend days and are excluded
so the count remains at 5 until Monday, when the count increases to 6

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#5
September 17, 2018 at 14:41:22
I'm an idiot. I was using July dates while looking at a September calendar.

Never mind. :-(

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
September 17, 2018 at 15:04:36
I can see where that would cause a bit of confusion. :-)

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Ask Question