Copy Date on Excel Tab to Cell In Spreadsheet and Add Year, How?

[aep002]

I work on bank reconciliations and the tabs say "7-19", "7-18" etc.
Figured out how to auto copy the tab data but how can I add to it so it
reads "7-19-06"?

THANKS!!!

 

[Steel Monkey]

Try This
Change the range to where you want the date displayed

Sub getname()
Dim name As Date
name = ActiveSheet.name
Range("D26").Select
ActiveCell.Value = name
ActiveCell.Select
Selection.NumberFormat = "d-mmm-yy"
End Sub

if i have misunderstood what you wanted let me know

 

[Steel Monkey]

Try This
Change the range to where you want the date displayed

Sub getname()
Dim name As Date
name = ActiveSheet.name
Range("D26").Select
ActiveCell.Value = name
ActiveCell.Select
Selection.NumberFormat = "d-mmm-yy"
End Sub

if i have misunderstood what you wanted let me know!

 

[aep002]

I can't use macros the bank doesn't like it. I tried formatting the
field as dd/mm/yy but it doesn't add the year when i copy the tab value
of "7-19".

 

[Biff]

Hi!

The file must already exist ( having been saved and given a file name):

=DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))

Format the cell as DATE

This will return the current year. If you need a specific year:

=DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&"-year_number")

Replace year_number as needed: 05, 1999, 2000, 02 etc.

Biff