Worksheet name change trigger dates to be entered by day names

[TJWheelz]

I have a workbook that contains sheets named with the Monday to Friday dates for a week. I would like the change of the sheet name to trigger a replacement of dates based on cell content and sheet name.

The sheet name is "02-13-23 to 02-17-23".

Column C hold day names.
I would like column B to auto populate with the corresponding date for the day for that week.

 

[Joe4]

Welcome to the Board!

Place this formula in cell B2, and format the cell in your desired date format:

=SUBSTITUTE(LEFT(TEXTAFTER(CELL("filename",A1),"]"),8),"-","/")+0