Convert Worksheet Name to Date in a Cell

[singincpa]

Hi,

I am attempting to automate some data entry in Excel. I have a workbook, and each sheet's name is a 6-digit date. I want to create a formula to add that sheet's name to a specific cell, but in a standard date format (m/dd/yyyy). For example, a sheet is named 083019. I want cell D2 to read the sheet name and convert it to the date 8/30/2019. Is this possible?

 

[Mentor82]

Hi,
It's impossible using excel formulas. However, this can achieved with VBA macro or by own developed function. Anyway, VBA programming is required.

Regards,
Sebastian

 

[jim may]

Paste this code into the code window of your ThisWorkbook Object
Then on a selected blank cell Double-Click it. HTH, Jim

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Value = Left(Sh.Name, 2) & "/" & Mid(Sh.Name, 3, 2) & "/" & "20" & Right(Sh.Name, 2)
End Sub

 

[singincpa]

Worked perfectly! Thanks so much!

 

[jim may]

Worked perfectly! Thanks so much!

Should work for next 80 years, till year 2100 when formula needs updating to 21 from 20. LOL!!
THANKS for your REPLY.
Jim