Worksheet name in a cell reference

Hi,
I'm trying to use a worksheet name in cell reference.
My current worksheet's name format is "JAN (28)" And I cannot change this.

But I want to change the date format to MMM-DD-YYYY. So, it will display in cell as JAN-28-2022

Please refer to the attached excel file.

If you can help that would be great.

Thank You.

This requires VBA not a formula, and I am not certain Jan (28) is a format that can be recognized as a long date without extensive coding.

You're awesome Fluff13.
Formula works great.
Thank you so much for your help.

Sorry, How can I convert Cell C2 into Julian date ?
I tried Julian date formula :

=TEXT(C2,"yyyy")&TEXT((C2 -DATEVALUE("1/1/"&TEXT(C2,"yy"))+1),"000")

but it doesn't work and give me #value error in cell F2.

Thank you in advance.

How about

Formula:

Please Login or Register  to view this content.

@Fluff13, Thank You so much for all your help.
You're the best.

Glad to help & thanks for the feedback.