Hi,
It is pretty easy to find the difference between 2 dates in excel "date 1 - date 2"..... but I'm not quite sure as to how one would do it while excluding holidays! Would be great if you know of any ways around it. Thanks
Hi,
It is pretty easy to find the difference between 2 dates in excel "date 1 - date 2"..... but I'm not quite sure as to how one would do it while excluding holidays! Would be great if you know of any ways around it. Thanks
Try to use the 'networkdays' formula
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Use the NETWORKDAYS.Intl function, with a list of holidays, if you want to include Saturdays.
See help for the third argument values to use.
=NETWORKDAYS.INTL(DATE(2014,7,1),DATE(2014,7,11),11,{"2014/7/4","2014/7/5"})
The 11 means Sundays are ignored, and 7/4 and 7/5 are holidays.
Last edited by Bernie Deitrick; 07-25-2014 at 03:18 PM.
Bernie Deitrick
Excel MVP 2000-2010
NETWORKDAYS.INTL has the ability to exclude holidays just like Bernie Deitrick says. The holidays that you want omitted are listed in a column and they are selected when you write formula.
The weekends are specified in the formula with:
1 or omitted Saturday, Sunday
2 Sunday, Monday
3 Monday, Tuesday
4 Tuesday, Wednesday
5 Wednesday, Thursday
6 Thursday, Friday
7 Friday, Saturday
11 Sunday only
12 Monday only
13 Tuesday only
14 Wednesday only
15 Thursday only
16 Friday only
17 Saturday only
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Or, you can define which days are to be evaluated as "weekend" days by using a 7 character string of 1s and 0s where 1s are evaluated as the weekends and 0s are evaluated as workdays.
The 7 characters represent the weekdays starting from Monday thru Sunday.
For example, this means the weekends are Monday and Thursday:
=NETWORKDAYS.INTL(start_date,end_date,"1001000")
If you're a member of the U.S. Congress then this means your weekends are Monday, Tuesday, Friday, Saturday and Sunday:
=NETWORKDAYS.INTL(start_date,end_date,"1100111")
The NETWORKDAYS.INTL function was introduced in Excel 2010.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hello I can't use workdays.int or networkdays function in my computer. Maybe it's new at the 2010/Excel. How can I use this function as macro code?
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
awesome.... thanks a lot guys...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks