• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is it possible count the number of work days only in a range?

Frncis

Frncis

Member
Column Range AI5:AI30 is sunday; AO5:AP30 is Saturday & Sunday, & AV5:AV30 is Saturday. Range AJ5:AN30 is a weekday range & AQ5:AU5 is a second weekday range.
the following formula counts all the days that are less than today, including weekends. The formula even accounts for holidays. =COUNTIF(AI5:AV30,">"&TODAY())-10.
 
Francis

Have you used the Networkdays or Networkdays.Intl functions

They allow you to count "Work" days between dates and allow for various weekend formats and allows for a List of Holidays
 
Hui said:
Francis

Have you used the Networkdays or Networkdays. Intl functions

They allow you to count "Work" days between dates and allow for various weekend formats and allows for a List of Holidays
Click to expand...
I have a dumb question. How do you enter the list of Holidays, since some of the federal holidays float?
 
Either as a Cell Range, Named Formula or Array List

Cell range: =NETWORKDAYS.INTL(A1,A2,1,A5:A14)

Named formula: =NETWORKDAYS.INTL(A1,A2,1,Holiday_List)
where Holiday_List = A5:A14

Array list: =NETWORKDAYS.INTL(A1,A2,1,{"1/5/2018","6/10/2018","10/10/2018"})

The weekends can also be defined using a string
eg: =NETWORKDAYS.INTL(A1,A2,{1,0,0,0,0,0,1},A5:A14)

where the first 1 is Sunday and the Last 1 is Saturday
So you can have a 3 day week like:
=NETWORKDAYS.INTL(A1,A2,{1,0,1,0,1,0,1},A5:A14)
ie: You only work Monday, Wednesday and Friday
 
Last edited:
Hui said:
Either as a Cell Range, Named Formula or Array List

Cell range: =NETWORKDAYS.INTL(A1,A2,1,A5:A14)

Named formula: =NETWORKDAYS.INTL(A1,A2,1,Holiday_List)
where Holiday_List = A5:A14

Array list: =NETWORKDAYS.INTL(A1,A2,1,{"1/5/2018","6/10/2018","10/10/2018"})

The weekends can also be defined using a string
eg: =NETWORKDAYS.INTL(A1,A2,{1,0,0,0,0,0,1},A5:A14)

where the first 1 is Sunday and the Last 1 is Saturday
So you can have a 3 day week like:
=NETWORKDAYS.INTL(A1,A2,{1,0,1,0,1,0,1},A5:A14)
ie: You only work Monday, Wednesday and Friday
Click to expand...
Ok. I understand what you are saying. I did do the Holiday list as a cell range. The only thing I don't understand is how floating holidays are handled. In other words, how does the system know that a holiday is a floating holiday, I.e. Veteran's day (2018). Nov 11 Is Veteran's Day (Sunday), but was observed on Nov12 (monday)
 
It doesn't
You need to define the dates within the cells yourself

But you can get a list of dates of holidays of many web sites
So setup a range for the next 5 years of known dates
then sit back for 4.9 years and enjoy
 
Hui said:
It doesn't
You need to define the dates within the cells yourself

But you can get a list of dates of holidays of many web sites
So setup a range for the next 5 years of known dates
then sit back for 4.9 years and enjoy
Click to expand...
Does it deduct the holidays from the number of days, or only on a week day?
Sorry, that I am asking so many dumb questions.
 
Last edited:
You must log in or register to reply here.
Back
Top