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