Hi Joel,

Sorry, I did not specify that the cell reference is referred to another
workbook that the filename just capture the path of the local workbook not
the source cell reference's path. The story is that I need to compare the
daily profit variance while the daily profit file is saved as PL0102.xls,
PL0202.xls, PL0302.xls and so on. I use a workbook to link to each PL
workbook and just replace the filename. Then I want make use the file name
to make the date.

thanks in advance.

Fanny

"Joel" wrote:

=mid(cell("filename",A3),4,4)

cell will get the filename inthe cell A3, then mid will extract starting at
character 4 and extract 4 characters.

To get a date format you probably want to use the date function
=date(2007,text(mid(cell("filename",A3),4,2),"gene ral"),text(mid(cell("filename",A3),6,2),"general") )

"Fanny" wrote:

Dear Helpers,

I have a cell reference as [PL0502.xls]Sheet1!$B$2. How can I extract the
information "0502" from it so that I can make a date from it?

thanks in advance.

Fanny