Gantt Type Chart - with multiple items same line

[ejsdon]

Hi All,

I am attempting to display hire dates for some items - but I need to include mobilisation / demobilisation durations on the same line - and to display almost as a Gantt Chart.

I have attached an example of how I would like the finished sheet to look (kind of - I will make it look prettier), but manually entered the values and colours that I would like automated when I enter the dates in the appropriate columns.

I have searched through online templates and cannot find one which provides this functionality. Does anyone know where I could find such a template, or what formula I could enter in the chart area to give the result I am looking for?

Thanks in advance, Elliot.

[Keebellah]

Hi, I don't think it's that difficult, just some brain tickling and logic will probably get it done.
I think I understand what you want the yellow is the start and demob the other is the actual period.
Don't hold your breath, I'll play around with it later this evening so maybe someone else picks it up too, but I'll update as soon as I have something for you

[ejsdon]

Hi Keebellah,

Thank You for your response.

Yes, the dates from mobilisation start up to the first hire date - I want those cells one colour. And again, the day after the last hire date to the demobilisation end.
I am happy to have an additional column(s) where I can enter the mob / demob durations if that helps?

I hope this makes sense!

[Keebellah]

It makes sense, I’ll see what I can do for you after dinner

[MrShorty]

If I understand, it looks like it should be two conditional format rules (if you are uncertain how to set up conditional formats: https://www.excel-easy.com/data-****...ormatting.html ):

1) If between mobilisation start and demobilisation end, color cell bright yellow =AND($D6<=H$5,H$5<=$G6)
2) If between hire start and hire end, color cell dark yellow. =AND($E6<=H$5,H$5<=$F6)
3) A formula in the cells would return 1 if between hire start and hire end and null string if anything else. =IF(AND($E6<=H$5,H$5<=$F6),1,"")

[Keebellah]

This is with three conditional format rules and no formula in the worksheet, just based upon the entered dates

[Keebellah]

Good morning Elliot,
I've played around with your file and made a slight modification.
Just enter the starting date in the row with the month and the rest is updated so it's dynamic
Now you can enter any schedule in the rows, the last row covered by the conditional format in columns H-AL is row 30
When you enter the four days the Gantt chart 'moves' according to the dates in the top row.
In the sample I added something starting somewhere in October and ending in November
No extra formulas just conditional formatting all you have to change if you need more rows is edit the conditional format for the block H6-AL30 and change the 30 to a higher or lower number of rows as needed

[ejsdon]

Thank You both @MrShorty and @Keebellah for your help, appreciated. I have created something useful.

[Keebellah]

Happy coding

[Keebellah]

Just for fun
I added a formula in H5 that always starts on a Monday 2 weeks prior to the current date, The slider above increments that date with 7 days up to 6 weeks