Month and date
 

Here is my problem

The way it works is that this formula is sat in AG251 (or whichever one)
=IF(AF251="Ongoing","July",IF(AB251="Risk -
Action","January",TEXT(DATEVALUE("01-"&AF251&"-1900")+((MOD(MONTH(DATEVALUE("01-"&AF251&"-1900")),3)=0)+1)*31,"mmmm")))

It then looks at what month is input in AF251 and then puts the relevant
month of review which is based around these rules:

Due
Month of review
Risks with actions due in Nov December
Risk-Action
January
Risks with actions due in December or January February
Risks with actions due in February March
Risks with actions due in March or April May
Risks with actions due in May June
Risks with ongoing actions July
Risks with actions due in June or July August
Risk with actions due in August September
Combined effect of actions October
Risks with actions due in September or October November

The current formula does this but instead of the input for the formula being
a month I would like it to be an actual date where it is clever enough to
pick up what month it is.

I am looking into maybe having a lookup table feeding off the actual date
(using the MONTH function as well) with the months and a value next to them
of 1-12 but I am having trouble setting this up.

Month and date
 

=IF(AF251="Ongoing","July",IF(AB251="Risk -
Action","January",TEXT(DATE(1900,MONTH(AF251)+1+(M OD(MONTH(AF251),3)=0),1),"mmmm")))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Pasty" wrote in message
...
Here is my problem

The way it works is that this formula is sat in AG251 (or whichever one)
=IF(AF251="Ongoing","July",IF(AB251="Risk -
Action","January",TEXT(DATEVALUE("01-"&AF251&"-1900")+((MOD(MONTH(DATEVALUE("01-"&AF251&"-1900")),3)=0)+1)*31,"mmmm")))

It then looks at what month is input in AF251 and then puts the relevant
month of review which is based around these rules:

Due
Month of review
Risks with actions due in Nov December
Risk-Action
January
Risks with actions due in December or January February
Risks with actions due in February March
Risks with actions due in March or April May
Risks with actions due in May June
Risks with ongoing actions July
Risks with actions due in June or July August
Risk with actions due in August September
Combined effect of actions October
Risks with actions due in September or October November

The current formula does this but instead of the input for the formula
being
a month I would like it to be an actual date where it is clever enough to
pick up what month it is.

I am looking into maybe having a lookup table feeding off the actual date
(using the MONTH function as well) with the months and a value next to
them
of 1-12 but I am having trouble setting this up.