Extract date range from cell and populate 'calendar-style' in separate tab

Welcome to the forum.

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Do NOT try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Thanks Pete, have attached workbook and edited my explanation. Any thoughts would be greatly appreciated!

It's getting a bit late here, so I need to go to bed. A few questions first, though:

Why are there 2 columns for each contractor in the Contractor Planning sheet?

What exactly do you want to show in that sheet? You refer to Jobsite A in the explanation, but I can't see where that data would be located.

It would be handy if you could manually show what you would like to see from your sample data, and I'll pick this up tomorrow.

Cheers for now.

Pete

Thanks Pete

I have now removed the second unnecessary column for each contractor.

Attached is what I would like to achieve. Please disregard "Jobsite A".

I've set this up in the attached file.

On the Client Planner sheet I have added a list of Contractors in column T, and set this up as a named range covering the coloured area. You can just add new contractors to the bottom of the list (I've added Joe Bloggs (coloured red) for testing).

A formula in column M gives a cumulative total of the days allocated. This needs to be copied down at least to the bottom of your data, but it doesn't matter if you copy further (I've copied to row 20 in the attached file).

Three formulae in columns O, P and Q expand the data so that there is one entry for every date used in the ranges, and determines the contractor that the date applies. These three formulae need to be copied down until you start to see hyphens in column O, to ensure that all the data is accounted for - I've copied them down to row 100.

A formula in D2 of the Contractor Planner sheet can then extract the appropriate client name and locate it under the contractor on the appropriate dates. I have copied the formula across into the other (alternating) columns up to column X, and copied the formulae down to the end of February - obviously, you'll need to copy them further down beyond that date. I've also tidied up the conditional formatting rules, so they cover up to row 400.

I've put some test data in red, and all seems to work fine.

Hope this helps.

Pete

Just to return this to the same layout as you had before, with three empty rows at the top of the Client sheet.

Hope this helps.

Pete