A curly date problem
 

I have the following formula

=IF(A4="","EMPTY",IF(J1D1,"Expired
"&DATE(YEAR(D4),MONTH(D4),DAY(D4)),DATE(YEAR(D4),M ONTH(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

A curly date problem
 

Maybe

=IF(A4="","EMPTY",IF(J1D1,"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

"curiosity_killed_the_cat" wrote:

I have the following formula

=IF(A4="","EMPTY",IF(J1D1,"Expired
"&DATE(YEAR(D4),MONTH(D4),DAY(D4)),DATE(YEAR(D4),M ONTH(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

A curly date problem
 

Try this:

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

Hope this helps.

Pete

On Nov 8, 9:22 am, curiosity_killed_the_cat
wrote:
I have the following formula

=IF(A4="","EMPTY",IF(J1D1,"Expired
"&DATE(YEAR(D4),MONTH(D4),DAY(D4)),DATE(YEAR(D4),M ONTH(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

A curly date problem
 

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

"Mike H" wrote:

Maybe

=IF(A4="","EMPTY",IF(J1D1,"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

"curiosity_killed_the_cat" wrote:

I have the following formula

=IF(A4="","EMPTY",IF(J1D1,"Expired
"&DATE(YEAR(D4),MONTH(D4),DAY(D4)),DATE(YEAR(D4),M ONTH(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

A curly date problem
 

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

"curiosity_killed_the_cat" wrote:

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

"Mike H" wrote:

Maybe

=IF(A4="","EMPTY",IF(J1D1,"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

"curiosity_killed_the_cat" wrote:

I have the following formula

=IF(A4="","EMPTY",IF(J1D1,"Expired
"&DATE(YEAR(D4),MONTH(D4),DAY(D4)),DATE(YEAR(D4),M ONTH(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