Cannot make (with conditional formatting) a from to until Gantt-chart.

Dear Excel specialists,

As an Excel newbie I have the following question…

I've created a Gantt-chart template (with support of this forum) that shows a timeline that’s based on conditional formatting. This conditional formatting uses the current date (date now) and the planned (until) date. The last one is manually entered.

What I like to have/achieve is a time-line with a (manually entered current date) and a (manually end date). So the yellow marking is not fixed to the (current date).
Is this easy to create?

Please see the attached file. The first block in this example works based on the current date. The second block I've just painted to give an example.

Thank you very much in advance! :-)

Best regards,

Hans

PS.
The date formatting is dd/mm/yyyy

It is easier to work with dates, rather than Q1, Q2 etc., so suppose you have a date of 1st January, 1st April, 1st July and 1st October for the chosen years in the row immediately above where the years are (i.e. row 18, which can be hidden or painted with a white font if you don't want to see them).

Then your CF condition relates to the start date being less than or equal to the date in row 18 AND the end date is greater than or equal to the date, applied across the whole block.

Let us know if you need any further help.

Hope this helps.

Pete

Hi Pete,

Thank you for the quick reply.
Unfortunately Q1-Q2-Q3-Q4 is a mandatory requirement in my company. Reporting is always based on quarterly reports.
So, if you have a formula (or an Excel-example), that would be great. :-)

Again thank you!

Best regards,


Hans

You already had a date function from C18 and across - I've just amended it slightly to give you the start dates for each quarter.

In the attached file I've set up the conditional format for you, using this formula:

=AND($A21<D$18,$B21>=C$18)

applied to the whole block. I changed the colour slightly so you can see it in operation.

Hope this helps.

Pete

Hi Pete,

This is exactly what I was looking for. Many, may thanks! :-) :-)
Now I can continue with filling all work related activities.

Best regards & have nice weekend,

Hans