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

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" <(E-Mail Removed)> wrote in message
news:3E87A9E9-F885-45F5-A28B-(E-Mail Removed)...
> 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
>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:3E87A9E9-F885-45F5-A28B-(E-Mail Removed)...
> > 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
> >
>
>
>

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



"Boenerge" <(E-Mail Removed)> wrote in message
news:6F4B028A-757F-41FD-BDB5-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:3E87A9E9-F885-45F5-A28B-(E-Mail Removed)...
>> > 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
>> >
>>
>>
>>