Formatting of the Returned Value of a Formula Q

  • Thread starter Thread starter John
  • Start date Start date
J

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


Frank Kabel said:
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"))))
Click to expand...
 
Back
Top