+ Reply to Thread
Results 1 to 4 of 4

Exclude weekends from monthly dynamic calendar

  1. #1
    Registered User
    Join Date
    11-16-2021
    Location
    oshawa
    MS-Off Ver
    newest version
    Posts
    3

    Exclude weekends from monthly dynamic calendar

    Hello,

    Ive created a monthly and yearly dynamic calendar with the formula =DATE(A2,A1,1) and used a combo box to get the year to generate. I was wondering how I would be able to filter weekends out of this calendar.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Exclude weekends from monthly dynamic calendar

    For subsequent dates you could just use this in A4:

    =A3+1

    and copy down.

    However, if you want to skip over weekends completely, you could do this:

    =A3 + IF(WEEKDAY(A3,2)=5, 3,1)

    This does not take account of the first date being either a Saturday or a Sunday, so you would need to apply something similar in A3.

    Hope this helps.

    Pete

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Exclude weekends from monthly dynamic calendar

    If your weekends are Sat & Sun, you could use the Workday function
    Excel 365 (Windows) 32 bit
    A
    B
    1
    11
    2
    2021
    3
    01/11/2021
    =WORKDAY(DATE(A2,A1,0),1)
    4
    02/11/2021
    =WORKDAY(A3,1)
    5
    03/11/2021
    =WORKDAY(A4,1)
    6
    04/11/2021
    =WORKDAY(A5,1)
    7
    05/11/2021
    =WORKDAY(A6,1)
    8
    08/11/2021
    =WORKDAY(A7,1)
    9
    09/11/2021
    =WORKDAY(A8,1)
    10
    10/11/2021
    =WORKDAY(A9,1)
    11
    11/11/2021
    =WORKDAY(A10,1)
    Sheet: Main

  4. #4
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Exclude weekends from monthly dynamic calendar

    Try Network days to check if it is or isnt, this will enable you to exclude Holidays or special observed days if you need....

    This should be your SECOND DAY
    Please Login or Register  to view this content.
    I would use the same fashion to get the FIRST DAY as the month doesn't always start in the middle of the week...
    Please Login or Register  to view this content.
    -If you think you are done, Start over - ELeGault

+ 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. Creating a dynamic monthly meal calendar
    By Brunheas in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2021, 09:48 AM
  2. Need help with auto populating calendar to exclude weekends
    By spam3570 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2019, 12:45 PM
  3. Replies: 2
    Last Post: 10-15-2018, 04:16 PM
  4. Exclude weekends in chart
    By gnoke in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-18-2010, 08:03 AM
  5. calculation to exclude weekends
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-06-2005, 03:05 PM
  6. [SOLVED] calculation to exclude weekends
    By Need2Know in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  7. calculation to exclude weekends
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 04:05 AM

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