Putting the date and day in a cell based on filename and tab name

Bill Golden
Bill Golden used Ask the Experts™
on
I have an Excel worksheet (attached) that I would like cell I30 to derive the date and day from part of the filename and the entire tabname.

For example, the attached filename is DTL-EE-1711-Rev3 with Tab named 02

I would like I30 to derive the year and month (1711) from the filename and the day (02) from the tabname.
AND print I30 something like

Thursday, November 2, 2017
or
November 2, 2017 - Thursday
or, if necessary
11-02-17 - Thursday

For internal security reasons, I would prefer this is done with VBA instead of an Excel formula.

Obviously, by the end of the month, there will be Tabs 01, 02, 03, 04 ... through 30 or 31 skipping Saturday and Sunday.
DTL-EE-1711-Rev4.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Test your restores, not your backups...
Top Expert 2016
Commented:
Try this please (adjust format specification as needed):

Public Function TabDate()
    a = Split(Split(ThisWorkbook.Name, ".")(0), "-")
    TabDate = Format(DateSerial(Left(a(UBound(a)-1), 2), Right(a(UBound(a)-1), 2), Application.Caller.Worksheet.Name), "dddd, mmmm dd, yyyy")
End Function

Open in new window


»bp
Bill GoldenExecutive Managing Member

Author

Commented:
Worked perfectly.  Thanks, Bill

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial