Dynamic calendar in Excel (formulas only) multiple-day projects

Hi all,

I am trying to create a calendar-based version of our "tracker" which is a list of all our upcoming projects and associated data. Some of the projects run multiple days and so there are some days with multiple projects scheduled. I need to coordinate project foremen so it's important to be able to see that a project runs across multiple days. We're using Excel Online with several users online at a time, so macros/VBA are out (formulas only), and I'm actually doubtful that a successful implementation will run smoothly on Excel Online (array formulas take a lot of processing power as you know).

I've had success getting the StartDate onto the calendar for every project, however I haven't been able to figure out how to show that a project stretches across multiple days. In my mind, the formula in the first entry of the day ($C$3 for example) needs to check to see if there is a project in the day before that is unfinished, and paste that project's data in accordingly. Then the cell below it ($C$4, 2nd project of the day) will do the same, checking to see if the cells above it are already taking care of multiple-day projects in the process.

Of course, if you have a less-elegant solution in mind that still works, I'm all ears! I appreciate your help.

Attachment: https://www.excelforum.com/attachmen...1&d=1489764418

You only seem to want to display the ProjectNum in your Calendar sheet - is that correct?

If so, I can amend one of my calendar files for you - I'll post later.

Pete

Okay, example file is attached.

I've put your sample data in the Data sheet, cols A to E, and I've added two sets of helper columns, as follows:

Columns G and I (in green) check the number of days in the date range for each record, and then derive the cumulative days. Columns K to N (in blue) expand each date range into a single entry for each day of the range. These formulae work automatically, so that you just need to enter new data into your table in columns A to E, but you do need to ensure that the formulae are copied down sufficiently far to pick up all the data.

In the Calendar sheet you can select the month and year of interest using the drop-downs in cells K5 and K6, and the display will automatically adjust, showing up to 10 entries per day. I've combined the ProjectNum with the Foreman's name so it will be easier to identify each project. If you want to avoid displays over a weekend, e.g. 15th to 18th April 2017, then you should ensure your start and end dates only occur on weekdays, having two or more entries if necessary. Note that in order to remove "orphan" dates at the end of a month, these will appear on the first line where necessary (e.g. 30th April 2017) - these can only appear on Sunday and Monday, where appropriate.

Feel free to play about with it, and to get back to me if you have any queries.

Hope this helps.

Pete

Fantastic, thank you! I did indeed want to include more information (project number, foreman (if assigned), and project description) but I figured I'd be able to adapt my needs around any responses. I really appreciate it, this will prevent a fair number of headaches!

Glad to be able to help.

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

Also, since you are relatively new to the forum, you might like to know 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

Welcome to the Forum.

If you read the Forum Rules at the top of the screen you will see that Rule 02 relates to hijacking someone else's thread. You should not do this - rather, you should start your own thread with a link back to this one if you feel it is relevant.

Pete

Originally Posted by ObviousAlias

I'm curious if you know of any way, using your example calendar as a starting point, to have it so that an item stays on the same row in a given week. In other words, 39811425-James starts on 4/15/2017 on the fifth row, and completes on 4/18/2017 on the top row, as other projects in this range complete, the placement of this specific project moves up to the top row in that day. If I were wanting to keep a project on the same row throughout the duration of the project, would this be possible?

Unfortunately, I haven't been able to devote a whole lot more attention to this project, but this kind of feature would definitely be appreciated around my company. I'd also like to have a Foreman's Calendar where projects are assigned to rows according to the foreman assignment (easy to look for your own work) but that's been put on the back burner as well. For now the solution that @Pete_UK gave has been working well. Please do chime in if you find a solution!