I have a series of formulae that I use to create a validation table of
dates (from 3 months ago to 12 months ahead) that in theory should
never need updating no matter when the spreadsheet is used. These work
fine, but I would like to end up with a display in the format mmm-yyyy.
I have tried setting this on the formulae cells and on the cell to be
validated to no avail, I have tried changing the formulae and still
have had no success. Can anyone offer a solution please ?

The formulae are as follows :
=IF(MONTH(NOW())3,MONTH(NOW())-3&"-"&YEAR(NOW()),12+MONTH(NOW())-3&"-"&YEAR(NOW())-1)
=IF(MONTH(NOW())2,MONTH(NOW())-2&"-"&YEAR(NOW()),12+MONTH(NOW())-2&"-"&YEAR(NOW())-1)
=IF(MONTH(NOW())1,MONTH(NOW())-1&"-"&YEAR(NOW()),12+MONTH(NOW())-1&"-"&YEAR(NOW())-1)
=MONTH(NOW())&"-"&YEAR(NOW())
=IF(MONTH(NOW())11,MONTH(NOW())-11&"-"&1+YEAR(NOW()),MONTH(NOW())+1&"-"&YEAR(NOW()))
=IF(MONTH(NOW())10,MONTH(NOW())-10&"-"&1+YEAR(NOW()),MONTH(NOW())+2&"-"&YEAR(NOW()))
=IF(MONTH(NOW())9,MONTH(NOW())-9&"-"&1+YEAR(NOW()),MONTH(NOW())+3&"-"&YEAR(NOW()))
=IF(MONTH(NOW())8,MONTH(NOW())-8&"-"&1+YEAR(NOW()),MONTH(NOW())+4&"-"&YEAR(NOW()))
=IF(MONTH(NOW())7,MONTH(NOW())-7&"-"&1+YEAR(NOW()),MONTH(NOW())+5&"-"&YEAR(NOW()))
=IF(MONTH(NOW())6,MONTH(NOW())-6&"-"&1+YEAR(NOW()),MONTH(NOW())+6&"-"&YEAR(NOW()))
=IF(MONTH(NOW())5,MONTH(NOW())-5&"-"&1+YEAR(NOW()),MONTH(NOW())+7&"-"&YEAR(NOW()))
=IF(MONTH(NOW())4,MONTH(NOW())-4&"-"&1+YEAR(NOW()),MONTH(NOW())+8&"-"&YEAR(NOW()))
=IF(MONTH(NOW())3,MONTH(NOW())-3&"-"&1+YEAR(NOW()),MONTH(NOW())+9&"-"&YEAR(NOW()))
=IF(MONTH(NOW())2,MONTH(NOW())-2&"-"&1+YEAR(NOW()),MONTH(NOW())+10&"-"&YEAR(NOW()))
=IF(MONTH(NOW())1,MONTH(NOW())-1&"-"&1+YEAR(NOW()),MONTH(NOW())+11&"-"&YEAR(NOW()))
=IF(MONTH(NOW())=1,MONTH(NOW())&"-"&1+YEAR(NOW()),MONTH(NOW())&"-"&1+YEAR(NOW()))

Thanks for any help
Regards
Fred

Hi Fred

The straight answer to your question is to wrap your formulae within a
TEXT(value,format) statement

=TEXT(IF(MONTH(NOW())3,MONTH(NOW())-3&"-"&YEAR(NOW()),
12+MONTH(NOW())-3&"-"&YEAR(NOW())-1),"mmmm-yyyy")

You could simplify things a bit though by using the one formula copied
down for 15 rows

=TEXT(DATE(YEAR(NOW()),MONTH(NOW())-4+ROW(1:1),1),"mmmm-yyyy")

--
Regards

Roger Govier


"Fred" wrote in message
oups.com...
I have a series of formulae that I use to create a validation table of
dates (from 3 months ago to 12 months ahead) that in theory should
never need updating no matter when the spreadsheet is used. These
work
fine, but I would like to end up with a display in the format
mmm-yyyy.
I have tried setting this on the formulae cells and on the cell to be
validated to no avail, I have tried changing the formulae and still
have had no success. Can anyone offer a solution please ?

The formulae are as follows :
=IF(MONTH(NOW())3,MONTH(NOW())-3&"-"&YEAR(NOW()),12+MONTH(NOW())-3&"-"&YEAR(NOW())-1)
=IF(MONTH(NOW())2,MONTH(NOW())-2&"-"&YEAR(NOW()),12+MONTH(NOW())-2&"-"&YEAR(NOW())-1)
=IF(MONTH(NOW())1,MONTH(NOW())-1&"-"&YEAR(NOW()),12+MONTH(NOW())-1&"-"&YEAR(NOW())-1)
=MONTH(NOW())&"-"&YEAR(NOW())
=IF(MONTH(NOW())11,MONTH(NOW())-11&"-"&1+YEAR(NOW()),MONTH(NOW())+1&"-"&YEAR(NOW()))
=IF(MONTH(NOW())10,MONTH(NOW())-10&"-"&1+YEAR(NOW()),MONTH(NOW())+2&"-"&YEAR(NOW()))
=IF(MONTH(NOW())9,MONTH(NOW())-9&"-"&1+YEAR(NOW()),MONTH(NOW())+3&"-"&YEAR(NOW()))
=IF(MONTH(NOW())8,MONTH(NOW())-8&"-"&1+YEAR(NOW()),MONTH(NOW())+4&"-"&YEAR(NOW()))
=IF(MONTH(NOW())7,MONTH(NOW())-7&"-"&1+YEAR(NOW()),MONTH(NOW())+5&"-"&YEAR(NOW()))
=IF(MONTH(NOW())6,MONTH(NOW())-6&"-"&1+YEAR(NOW()),MONTH(NOW())+6&"-"&YEAR(NOW()))
=IF(MONTH(NOW())5,MONTH(NOW())-5&"-"&1+YEAR(NOW()),MONTH(NOW())+7&"-"&YEAR(NOW()))
=IF(MONTH(NOW())4,MONTH(NOW())-4&"-"&1+YEAR(NOW()),MONTH(NOW())+8&"-"&YEAR(NOW()))
=IF(MONTH(NOW())3,MONTH(NOW())-3&"-"&1+YEAR(NOW()),MONTH(NOW())+9&"-"&YEAR(NOW()))
=IF(MONTH(NOW())2,MONTH(NOW())-2&"-"&1+YEAR(NOW()),MONTH(NOW())+10&"-"&YEAR(NOW()))
=IF(MONTH(NOW())1,MONTH(NOW())-1&"-"&1+YEAR(NOW()),MONTH(NOW())+11&"-"&YEAR(NOW()))
=IF(MONTH(NOW())=1,MONTH(NOW())&"-"&1+YEAR(NOW()),MONTH(NOW())&"-"&1+YEAR(NOW()))

Thanks for any help
Regards
Fred

Sweeeeeeeet, Thanks Roger.

Regards
Fred