Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Expand curly bracket symbol over multicells | Excel Discussion (Misc queries) | |||
How do I obtain Curly quotes and apostrophes in Excel? | Excel Discussion (Misc queries) | |||
Getting rid of curly brackets within formula | Excel Worksheet Functions | |||
Getting rid of curly brackets within formula | Excel Worksheet Functions | |||
Getting rid of curly brackets within formula | Excel Worksheet Functions |