Dated Tab Name into Cell

[lapta301]

Dear All

Could I trouble you for some help bringing the tab value into a cell.

I have dated tabs in the format 2011-02-20 and would like a formula in say A3 to bring in the sheet date in the format dd-mmm-yy.

As ever my grateful thanks for your help.

 

[JamesW]

Hi there,

This will put each sheet name in cells A3 -> A (total number of sheets):

Sub test()
    For i = 1 To Sheets.Count
        Range("A" & i + 2).Value = Sheets(i).Name
    Next
End Sub

Or if you only have 1 sheet you want to use:

Sub test()
    Range("A3").Value = Sheets("2011-02-20").Name
End Sub

 

[Richard Schollar]

Here's a formula:

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")+0

Format the formula cell as a date

 

[GlennUK]

This will get you the text like that:

=TEXT(DATE(LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999),4),MID(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999),6,2),RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999),2)),"dd-mmm-yy")

Or you could remove the TEXT( and ,"dd-mmm-yy") parts to generate an Excel date serial number and format the cell to be dd-mmm-yy instead.

 

[lapta301]

Richard, GlennUK & JamesW

That's great thank you all so much for such a quick varied response.

Steve