Formatting of the Returned Value of a Formula Q

[John]

I have the following formula below which I'm trying to format with regards
to the value returned from EndPeriod. Visble in the returned value field is
2002-19, but when it is returned through the formula below it returns
200219, I want it to display as 2002-19. I've tried inserting
TEXT(EndPeriod,"####-##") in various parts of the formula below but its not
working

Any ideas?

=IF(D9="","",IF(H9="No
Date","",IF(INDEX(EndPeriod,MATCH(D9,EmployeeNames,0))=0,"No
Date",INDEX(EndPeriod,MATCH(D9,EmployeeNames,0)))))

 

[Frank Kabel]

Hi John
the problem is that formulas can't change the format. even with using tEXT
you end up with converting a numeric value to a string value.
But if you want to go this way try:

=IF(D9="","",IF(H9="No
Date","",IF(INDEX(EndPeriod,MATCH(D9,EmployeeNames,0))=0,"No
Date",TEXT(INDEX(EndPeriod,MATCH(D9,EmployeeNames,0)),"0000-00"))))

 

[John]

Thanks Frank

Hi John
the problem is that formulas can't change the format. even with using tEXT
you end up with converting a numeric value to a string value.
But if you want to go this way try:

=IF(D9="","",IF(H9="No
Date","",IF(INDEX(EndPeriod,MATCH(D9,EmployeeNames,0))=0,"No
Date",TEXT(INDEX(EndPeriod,MATCH(D9,EmployeeNames,0)),"0000-00"))))