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

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
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.
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
Worked perfectly.  Thanks, Bill
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.