Auto Populate Excel Calendar based on Date Ranges

I am trying to have the calendar in the second tab of the attached excel file to auto populate based on info in the first tab.
In the first tab, there is the start and end dates of certain projects. The calendar will need to only show the project name of every project being worked on that specific day.
Some days will have more than one project being worked on, the Calendar will just need to list them all under that day. The Start and End dates count as days the project being worked on as well. We will also need the calendar to auto update whenever we modify the dates in the first tab or add/remove projects (rows).

Please let me know if there is a possible way to do this by formulas or are Macros the only solutions as I don't have any Macro experience.

Thanks all in advance.

In the attached file I've added some formulae in helper columns (in blue) in your Project_tracker sheet. These essentially expand your data to have one entry per day for each project. If you have more data, you should ensure that the formula in column F is copied down at least to the bottom of your data (I've copied it beyond to row 50, as indicated by the hyphens), and the formulae in columns H, I J and K should be copied down at least to the row given by the maximum number in column F (353 currently) - I've copied the formulae down to row 500, as can be seen by the hyphens in column H. If you are happy that the formulae have been copied far enough down the sheet, then you can hide the helper columns.

The Calendar sheet shows a monthly calendar, with the project names shown on the appropriate day(s) - up to 7 names can be accommodated on any one day. You can choose the month and year using the drop-downs in cells K3 and K4, and the calendar will automatically (and immediately) adjust itself.

The file is entirely driven by formulae, so there are no macros to enable. Obviously, you can apply your own fancy formatting to the Calendar sheet for headings etc.

Hope this helps.

Pete

Pete,
I think this is great.
Thanks a million for your help.

Mike

Glad to be able to help, Mike - thanks for feeding back.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Pete,
I have one more request/question.
I wanted to put a data validation for columns B and C (Start and Finish Dates) so it will require and esures that a date is always entered in these cells whenever a Project name exists in Column A.
Also if possible, could the Cells in B and C have a drop down Calendar to make it easier to select a date. The possible range of dates we can have in those columns are between 12/31/2011 to 12/31/2020.

Thanks in Advance,
Mike

I can't help you with that, Mike, as I'm going away tomorrow for a few days.

If you want a drop-down calendar you will have to use macros. I suggest you start a new thread in the programming forum, and perhaps someone else will help you with that.

Pete

Help me please with the following problem.
How should I write the formula in such a way that in the Saturday and Sunday I not have activities.
The file is calendar_auto_populate.xlsx.
Thanks very much!

If you read the Forum Rules you will see there is one about hijacking someone else's thread (Rule 02) - you should not do this, but instead you should start a thread of your own, linking back to this thread if you think it is relevant.

That being said, though, the easiest thing is to hide columns B and H on the Calendar sheet.

Pete

Thanks Pete,
To hide the columns is not a solution because I have activities on Saturday or Sunday. I was referring to the activities of an interval of time, but which should not appear on Saturday or Sunday.
=NETWORKDAYS(B3;C3)
Apologize for my mistake!

mikearmanios

To add to the project, select all the cells from A2 to D42 goes on conditional formatting and a new formula, puts this =MONTH($B2)=Calendar!$K$3 and padding with color of your choice, result when selecting the month calendar will fill coloring the selected line following example.

I have helped

Decio of Brazil excuses translation by Google Translator

Hi Pete,
How do I update your file so the calendar can go beyond 2018?

The years are selected using a data validation drop-down. If you select that cell and then click on Data | Data Validation (twice), you will see the years separated by commas. Just add new years to the end of this list, each separated by a comma. You can also remove some of the earlier years if you don't need them, and then click OK.

Hope this helps.

Pete

This was exactly what I needed. Thank you, Pete!

you are a beautiful mad genius, thank you