# 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

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

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 ?

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.

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.`

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

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!

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"

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1