Results 1 to 6 of 6

Thread: Dynamic Calendar

  1. #1
    Acolyte Francis Gerard Tupaz's Avatar
    Join Date
    Jun 2012
    Location
    Philippines
    Posts
    30
    Articles
    0
    Excel Version
    2011

    Dynamic Calendar



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear All,

    I am into sales and it is quite important for us to ensure that we get to cover our key customers on a monthly basis, depending on its frequency and objective.

    I have been trying to create a monthly calendar for my team, that will be easy enough to update and so far I already figured out how to find the DATE of the FIRST MONDAY of the MONTH, but FIRST DATE (1) doesn't always fall on a Monday and may fall in anyway of the week. Is there a way for me to capture the 1st DATE and DAY of the Month, by simply filling up the MONTH and YEAR?

    I attached my excel sheet for your viewing, review and comment.

    Thank you in advance.

    Francis
    Attached Files Attached Files

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,549
    Articles
    0
    Excel Version
    2010 on Xubuntu
    The first working day of a month is obtained with =WORKDAY(EOMONTH(C5,-1),1,Holidays) where Holidays is a range containing the year's holidays

    PS I would strongly advise to NOT use merged cells, they will become a nuisance in the future
    Thank you Ken for this secure forum.

  3. #3
    Acolyte Francis Gerard Tupaz's Avatar
    Join Date
    Jun 2012
    Location
    Philippines
    Posts
    30
    Articles
    0
    Excel Version
    2011
    Quote Originally Posted by Pecoflyer View Post
    The first working day of a month is obtained with =WORKDAY(EOMONTH(C5,-1),1,Holidays) where Holidays is a range containing the year's holidays

    PS I would strongly advise to NOT use merged cells, they will become a nuisance in the future
    Thanks for the help. If i may just ask...if my constant in the sheet are days (monday to sunday), is there a way for me to automatically display DATE 1 (1st date of the month) of the Month in the appropriate day?

    Example:
    January 1, 2019 falls on a Tuesday, can the date Jan 1 go automatically on the Tuesday constant header?

    Thanks!

    Francis

  4. #4
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,549
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Like this ?
    CALENDAR DYNAMIC-1.xlsx
    Be aware that I used J6 to define the first working day of the month. You can change or hide this of course.
    If you wish to make changes when you drag your formula to the right you have to drag the three cells at once . First select the group of three and then pull right
    Formulas in C9 and D9 are not to be dragged
    Thank you Ken for this secure forum.

  5. #5
    Acolyte Francis Gerard Tupaz's Avatar
    Join Date
    Jun 2012
    Location
    Philippines
    Posts
    30
    Articles
    0
    Excel Version
    2011
    Pecoflyer,

    You are an excel God!!!! Thank you.

    Francis
    Quote Originally Posted by Pecoflyer View Post
    Like this ?
    CALENDAR DYNAMIC-1.xlsx
    Be aware that I used J6 to define the first working day of the month. You can change or hide this of course.
    If you wish to make changes when you drag your formula to the right you have to drag the three cells at once . First select the group of three and then pull right
    Formulas in C9 and D9 are not to be dragged

  6. #6
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,549
    Articles
    0
    Excel Version
    2010 on Xubuntu
    You're welcome
    (please don't quote entire posts unnecessarily - Thx)
    Thank you Ken for this secure forum.

Posting Permissions

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