Excel, dynamic referencing dates

Question

I'm struggling to implement a dynamic reference within excel when using dates. The formula I'm using works with other strings but dates seem to be an issue.

=INDIRECT(C$2&"!$C2")

The first instance of C2 is equal to 04-01-2021, the same as the sheet (name) I want to reference. ?

The second instance of C2 pulls in a [Y]es or [N]o which works fin if the references / sheets are not dates.

Could someone point me in the right direction please?

Thanx for looking, Sam

EDIT: I've tried using another suggestion but I'm still getting no joy.

=INDIRECT("'"&TEXT(C2,"dd-mm-yyyy")&"'!"&C2)

This pulls in the date:

=TEXT(C2,"dd-mm-yyyy")

04-01-2021

What am I doing wrong with the indirect function?

EDITv2: I was going to upload an example but obviously I'm too dense to be able to find any sort of upload button / link on stackoverflow.

Answer 1

Figured it.

I was using dates and forgot to wrap my dates in speech marks for them to reference correctly.

Master Sheet C2 contents:

="04-01-2021"

Sheet name:

04-01-2021

Thanx for your help @Carter Bayer