Gantt Chart conditional formatting - multiple dates in cell

January 31, 2013 at 08:33:57
Specs: Windows 7

I have created a convoluted formula which responds true or false which is then formatted conditionally to colour-in cells in a date range.

Row 2 has a calendar year of dates (e.g. H2 = 31/01/13, H3 = 01/02/13, H4 = 02/02/13 and so on)

And columns have the date ranges:
Column F3 = 31/01/13
Column G3 = 05/02/13

Column H3 =AND(H$2>=$F3,H$2<=$G3)

Which therefore colour-in the cells which respond true to the relevant formula.

What I'd like to do is to have a monthly event occurring and coloured in - e.g.

F3 = 31/01/13; 28/02/13; 31/03/13
G3 = 02/02/13; 02/03/13; 02/04/13

So the result would be that "true" would come up in the relevant cells on and in-between these dates and therefore they would be formatted with colour!

"," and "AND" and ";" don't work - is there something else I could use?

Thanks ever so much!

See More: Gantt Chart conditional formatting - multiple dates in cell

January 31, 2013 at 12:53:59
Hi Charlotte

Convoluted is somewhat of an understatement even more so because of a confusion between what are rows and columns. For example, where you say;

Row 2 has a calendar year of dates (e.g. H2 = 31/01/13, H3 = 01/02/13, H4 = 02/02/13 and so on)

What you are actually describing is a column, specifically, column H and not, as you say, "Row 2"

Then you say "column F3=31/01/13" but F3 is a cell so we don't know if 31/03/13 appears in just that cell or do you mean that F3,F4,F5,F6 etc. all contain 31/03/13?

Perhaps you could post a part of your data here to better illustrate what your data looks like (use the "pre" button at the top of your reply when you paste your data in the post).

Report •

February 1, 2013 at 01:10:47
OK - sorry! I'm new to this and struggling. Take two... (think gantt chart!)

A2 = Activity, B2 = Start date (for example 31/12/13), C2 = End date (for example 01/01/13), D1 = 31/12/12, E1 = 01/01/13, F1 = 02/01/13 etc. etc.

D2=AND(D$1>=$B2,D$1<=$C2) - which returns "TRUE"
E2=AND(E$1>=$B2,E$1<=$C2) - which returns "TRUE"
F2=AND(F$1>=$B2,F$1<=$C2) - which returns "FALSE"

Then I format these true or false responses with conditional format so that they colour cells which are true and not those which are false!

What I'd like to know is can I insert multiple start dates in the "start date cell" and multiple end dates in the end date cell, so that it would return true in intervals along the row - which in turn would colour those cells and demonstrate activities which are repeated throughout the year...

Clearer? Hope so! Thanks for your help.

Report •

February 1, 2013 at 11:54:47
Hi Charlotte

I can see what you are trying to do. I'm wondering though whether you should be considering using Microsoft Project for this project. In MS Project there is a specific function for repeating activities that would automatically create the bars in the GANTT chart. I bring this up because what MS Project actually does is creates separate rows for each occurrence of that activity. It then allows you to "roll up" those separate rows so you can see the recurring activity on one line.

I bring all this up because trying to replicate what MS Project does in Excel would be complicated. Entering multiple dates in a single cell would not be easily recognised by excel as dates. Your options are to either create separate rows for the recurring activity or, more simply, create a single activity and enter a value for the recurrence under the appropriate date in row 1. You could enter something other than "TRUE" so that you can conditionally format those recurring dates with a different colour.

Or ... if you have MS Project, I would recommend trying that out. It has many features and functions specifically suited to doing this sort of thing.

I know this isn't the answer you were hoping for but hopefully it gives you some ideas for solving your problem.

Report •

Related Solutions

February 4, 2013 at 01:05:47
Thanks - I'll have a look - I don't think that we have MS project...

Report •

Ask Question