Spreadsheet Data into Calendar, Date Ranges, Highlighting Date Ranges

[Cap123]

Afternoon All,

I am working on a problem that is above my understanding and I feel like I am going in circles trying to find a workaround for my issue!

I have attached a sanitised copy of my workbook. What I am trying to achieve is the data from Task Sheet (Project Start Date and End Dates, and, if possible the Project Title) to be entered into the Calendar in the correct cells. On the Calendar worksheet each date is represented by a cell and I need the project task dates, when imported, to highlight the cells for the corresponding dates and then the title of the task (if possible) to be centered across the date range. E.g. Project 4 Task 1 runs from 10/01/2020 until 01/03/2020, so all the cells from 10/01/2020 until 01/03/2020 would be highlighted/filled with a colour and on Project 4's line in the Calendar. I have filled this in manually as an example of what I would like to achieve!

I can make this work if a specific task is entered into a specific cell within the Task sheet, however it needs to be dynamic and read down the list as the list will updated regularly which I cannot do! The VLOOKUP sheet contains some preliminary VLOOKUPs I thought would solve my problem.

I have tried using INDEX and MATCH, IF AND, and VLOOKUP functions but I haven't been able to make these work as intended!

Thank you for any help you can give!

[Pete_UK]

It's better to deal with dates in your Calendar sheet, which can be formatted to show only the day number. Then you can use conditional formatting to apply the colours, with a formula in each cell to check if that date is within the date range.

The awkward thing is to try to centre the title of the task - it would be easier if you showed this in another column on the left.

Are you only likely to have 4 projects, or would you need room for expansion?

Hope this helps.

Pete

[Cap123]

Pete,

Thanks for your reply! The problem is that I don't have much flexibility with the layout of the sheet as this needs to be reported in a specific format! The information in the Task sheet is not the only information in there, just what is relevant for my problem, so I (ideally) need to keep all the information in the same sheet.

There will only be 4 projects for the foreseeable future, but I should be able to expand it if I have something to replicate (hopefully!). If the title isn't possible then I can leave it for now!

Thanks for your help so far!

[Pete_UK]

Okay, can you explain what rows 2, 7, 12 etc. are for in your Calendar sheet? Does "Hol" stand for holiday, and if so where would this data come from?

Pete

[Cap123]

Pete,

You are correct, Hol is short for Holiday. My apologies, this was left over from a previous iteration of the sheet and is no longer needed!

Thanks for your help again!

[Pete_UK]

Take a look at the attachment in this thread from last year:

https://www.excelforum.com/excel-cha...e-to-date.html

or this one from a couple of years ago:

https://www.excelforum.com/excel-pro...ck-events.html

They both show simple Gantt charts, similar to what you want to achieve. The layout is slightly different to yours, in that the dates continue across the screen, but it is not difficult to split at each month end. Maybe you can see how the formulae are achieving the effect.

Post back if you need anything else.

Hope this helps.

Pete