Dynamic Calendar

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

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

Pecoflyer said:

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

Click to expand...

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

Like this ?
View attachment 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

Pecoflyer,

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

Francis

Pecoflyer said:

Like this ?
View attachment 8537
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

Click to expand...

You're welcome
(please don't quote entire posts unnecessarily - Thx)