Date formatting of formula created dates

[Fred]

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

 

[Roger Govier]

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")

 

[Fred]

Sweeeeeeeet, Thanks Roger.

Regards
Fred