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


"John" wrote:

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)))) )

Thanks Frank


"Frank Kabel" wrote in message
...
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" wrote:

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)))) )