Cell search & reporting

[Boenerge]

Hi
Is there a formula that would search one cell for a month and then search a
range of other cells for a date and then look below and report into the
formula cell what is reported possibly in a different colour depending on
what is reported.
e.g.

A B C D
E F G
1 Date 31…Month December…Year 2007 2
3 MON TUE WED THU FRI SAT SUN
4 31 1 2 3 4 5 6
5 smith E E DO S E DO DO
6 jones DO N DO DO DO DO N
7
8
9
The cell in A9 would search cell B3 to find the month required, then would
search cells A4 to G4 for the correct date e.g. recognise that the required
date is the 3rd of january in cell D4. It would then look below in jones's
line and report back S in red.
Thanks in advance
Jason

 

[Bernie Deitrick]

What do you mean by: "search cell B3 to find the month required"? And how
do you know that the correct date is January 4?

Bernie

 

[Boenerge]

Hi Bernie,
I mean to search the cell for the month, which in this case is December, The
formula should then pick up the fact that 31 December 2007 is the beginning
of the week, working out cell D4 as the 3rd of January
Thanks
Jason

 

[Bernie Deitrick]

Jason,

The formula

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,".",""),"Date
",""),"Month",""),"Year",""))

formatted for date, will return the date value found in the string

Date 31.Month December.Year 2007

(If that is truly how it is presented.)

Then you can use that value in a formula like this - let's assume that the
formula is in cell F3:

=DATE(YEAR(F3),MONTH(F3) + IF(DAY(F3)>D4,1,0),D4)

Not sure what you want to do after that...

Bernie