A curly date problem

[Guest]

I have the following formula

=IF(A4="","EMPTY",IF(J1>D1,"Expired
"&DATE(YEAR(D4),MONTH(D4),DAY(D4)),DATE(YEAR(D4),MONTH(D4),DAY(D4))))

This is used to name the work sheet. When false is the result it returns the
date as mmm-yyyy which in turn names the sheet using a macro, and it works
fine.

The true result works, almost, it returns "Expired+the date as a serial #",
minus the quotes, and no matter what I do I can't get it to return "Expired
mmm-yyyy". Is it possible to get the false result as "Expired mmm-yyyy"
instead of the serial date?

J1 has the today() formula
D1 is a date entered by the user in dd/mm/yy format

Hope I made sense

Thanks

 

[Guest]

Maybe

=IF(A4="","EMPTY",IF(J1>D1,"Expired
"&TEXT(DATE(YEAR(D4),MONTH(D4),DAY(D4)),"mmm:yyyy"),DATE(YEAR(D4),MONTH(D4),DAY(D4))))

Alter the date format to suit your needs

Mike

 

[Pete_UK]

Try this:

=IF(A4="","EMPTY",IF(J1>D1,"Expired
"&TEXT(DATE(YEAR(D4),MONTH(D4),DAY(D4)),"mmm-
yy"),DATE(YEAR(D4),MONTH(D4),DAY(D4))))

Hope this helps.

Pete

 

[Guest]

Thanks Mike

Works a treat, I had something close to that, but not close enough.

BTW for others reading I had to alter the seperator in the mmmyyyy from : to
- to have the sheet name function work properly

 

[Guest]

Your welcome and thanks for the feedback. I never noticed you were using this
to name the worksheet, a colon ) is a reserved character in worksheet names.

Incidentally why is "History" a reserved worksheet name, anyone know because
I've no idea.

Mike