Excel Help - Hard to explain

[HUNTER.P]

Right being a Scout leader means I like to include as many Scouts in a camp as possible trying to arrange dates around their own family holidays etc. So everytime a camp comes up I ask for the dates they are available to get a just of who is available when.

Now I thought of trying to show this in an excel sheet to see the best overlap. I ask you if there is an easier way of doing this, I have just written the dates in manually then filled in the corresponding cells.

But with the use of additional conditional formatting/graphs or other features can I make this job easier, where I can fill in dates and the cells with highlight themselves?

Any input is greatly appreciated

 

[A.N.Other]

Turn the date column headers to a proper date (i.e. 30 -> 30/3/12) and split the available date range into two cells - one for first available and another for last available. Then a simple if statement would do the trick:

=if(and(datecolheader>=firstavailable,datecolheader<=lastavailable), 1, "")

You'd have to $ it appropriately in order to be able to drag it around, of course. If you want to include the option for two available periods, then put an or() around the and() statement and duplicate the and() to reference the second set of firstavailable lastavailable dates:

=if(or(and(datecolheader>=firstavailable,datecolheader<=lastavailable), and(datecolheader>=firstavailable2,datecolheader<=lastavailable2)), 1, "")

 

[HUNTER.P]

Worked an absolute treat Thanks very much, should make it easier to visualise dates now when planning camps etc

For others that might find this useful, I changed the True value to "Y" and then used the "conditional formating too"l," highlight cells rules", "equal too". And if the cell is equal to Y then they are filled green, and made the text colour the same colour to hide it.

 

[A.N.Other]

No problem