+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    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. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,094

    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))
    Last edited by jeffreybrown; 11-05-2018 at 05:48 PM.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

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

    Quote Originally Posted by jeffreybrown View Post
    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. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,094

    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. #5
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

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

    Quote Originally Posted by jeffreybrown View Post
    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: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,094

    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. #7
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

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

    Quote Originally Posted by jeffreybrown View Post
    =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. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,094

    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"
    Attached Files Attached Files
    Last edited by jeffreybrown; 11-05-2018 at 06:41 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to calculate no. Of working days for the year 2018
    By Onditi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-26-2017, 06:00 AM
  2. [SOLVED] Calculate year month and days between two dates
    By Premmadaan in forum Excel General
    Replies: 7
    Last Post: 04-24-2017, 05:38 AM
  3. [SOLVED] calculate total number of days between two days based on 365 days year
    By aparunkumar in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-14-2016, 12:51 AM
  4. Replies: 7
    Last Post: 12-24-2014, 05:03 PM
  5. Replies: 1
    Last Post: 12-24-2014, 03:52 PM
  6. [SOLVED] Formula needed to calculate working days in specified month between two dates
    By webfeet2 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-24-2013, 02:19 AM
  7. Calculate Year Month and Days and Add them
    By lavan_joy in forum Excel General
    Replies: 8
    Last Post: 11-17-2010, 12:35 PM

Bookmarks

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