Gantt type chart using start & end dates with conditional formatting

Try this to solve your start and end date being in the same month:

=IF([@[Start Date]]>0,IF(OR(AND([@[Start Date]]<'LookUp Table'!$B$13,[@[End Date]]>'LookUp Table'!$D$12),MONTH([@[Start Date]])=MONTH([@[End Date]])),[@[% time]],""),"")

I've just checked to make sure you have something in the start date first, then added an OR around your original formula, with a second condition to check if the month is the same in both the start and end dates.

I'm not clear about what behaviour you expect to see if the start and/or end dates are part way through the month?

No problem - glad to have helped.

It looks like you already have shading in the mid-month end date rows. Check it out and let me know if you get stuck.

Hi,

Yep, sorry, I can see a couple of things I missed.

I've added some extra checks into the "current month only" so that it checks against the Lookup Table as well as itself. I've also offset the end date lookup by one row so that it checks the next one up which should solve your "ends in" issue.

I only updated it in the last four columns in the attached revised version so you'd need to copy it across and adjust the lookup values.

Fingers crossed

A small tweak should do it...

Where you currently have

,[@[End Date]]>='LookUp Table'!$L$2

in the second part of the first AND statement, change it to:

,[@[End Date]]>'LookUp Table'!$L$2

In other words, check GREATER THAN instead of GREATER THAN OR EQUAL TO

I think the problem crept in because I had to change the date it was checking against.

I downloaded your latest version and tried it on all columns and checked all the end dates and it looks OK to me (famous last words )

Just one thing to be careful of... the formula is only checking month numbers in some places; as your data only covered 12 months this was fine. If you want to extend the dates over multiple years, you will need to check YEAR numbers AND month numbers.

Hello again,

I got to thinking about that potential problem if you add more than 12 months worth of dates, and came up with an alternative solution.

This places a helper row on your main Resource Plan sheet which calculates the EOMONTH date for each of your column headings in your table (you can hide this row if it's in the way).

I've then simplified the formula which drives whether the % Time appears which in turn drives your CF. I changed your last column to April 2017 and the end date on the first row of your data, just to demonstrate that it works.

The advantages are that you don't need to check your lookup tables for dates, you won't need to adjust the formula in each column (it will always look at the helper cell directly above the column heading) and the solution is more robust if you want to extend beyond 12 months.

Take a look and see what you think (new version attached)

My pleasure... and thanks for the rep