# Formula to calculate working days and calendar days per month-year ?

1. ## Formula to calculate working days and calendar days per month-year ?

How to calculate the working days and calendar days for every month-year ?

My report looks like this

HTML Code:
``````                           Oct-2018     Nov-2018      Dec-2018    Jan-2019     Feb-2019     Mar-2019      Apr-2019     May-2019   Jun-2019     Jul-2019    Aug-2019    Sept-2019

Working Days                   23           22

Calendar Days                  31           30            31           31          28          31             30          31         30            30         30           30``````

When the date hits oct 1st,2019 the above template dates change from oct-2019 through sept-2020. Similarly 2020,2021,etc  Register To Reply

2. ## Re: Formula to calculate working days and calendar days per month-year ?

For working days: =NETWORKDAYS(A1,EOMONTH(A1,0))
For calendar days: =DAY(EOMONTH(A1,0))  Register To Reply

3. ## Re: Formula to calculate working days and calendar days per month-year ? Originally Posted by jeffreybrown For working days: =NETWORKDAYS(A1,EOMONTH(A1,0))
For calendar days: =DAY(EOMONTH(A1,0))

My date formula looks like this TEXT(DATE(YEAR(TODAY())-IF(MONTH(TODAY())>9,0,1),10,1),"MM-YY"))),0))

How can I include my dates in your formula ?  Register To Reply

4. ## Re: Formula to calculate working days and calendar days per month-year ?

Can you paste the actual working formula or your sample workbook. I can't get that formula to work.  Register To Reply

5. ## Re: Formula to calculate working days and calendar days per month-year ? Originally Posted by jeffreybrown Can you paste the actual working formula or your sample workbook. I can't get that formula to work.
This is my month year formula. It displays 10-18.

Formula:  `Please Login or Register  to view this content.`  Register To Reply

6. ## Re: Formula to calculate working days and calendar days per month-year ?

=networkdays(date(year(today())-if(month(today())>9,0,1),10,1),eomonth(date(year(today())-if(month(today())>9,0,1),10,1),0))
=day(eomonth(date(year(today())-if(month(today())>9,0,1),10,1),0))  Register To Reply

7. ## Re: Formula to calculate working days and calendar days per month-year ? Originally Posted by jeffreybrown =networkdays(date(year(today())-if(month(today())>9,0,1),10,1),eomonth(date(year(today())-if(month(today())>9,0,1),10,1),0))
=day(eomonth(date(year(today())-if(month(today())>9,0,1),10,1),0))
Thank you!  Register To Reply

8. ## Re: Formula to calculate working days and calendar days per month-year ?

On second thought...

In A1 >> =DATE(YEAR(TODAY())-IF(MONTH(TODAY())>9,0,1),10,1)
In B1 copied across to the right >> =DATE(YEAR(A1),MONTH(A1)+1,1)

Now in A2 >> =NETWORKDAYS(A1,EOMONTH(A1,0)) >> copy to the right
Now in A3 >> =DAY(EOMONTH(A1,0)) >> copy to the right

Highlight A1:A12

Custom Format
• Right click cell (or Ctrl + 1)
• Format Cells
• Number
• Custom
• Type: "MM-YY"  Register To Reply