How to convert Date to string? And Pull data from multiple webpages

[VAer]

This is a different question (following the earlier thread: https://www.excelforum.com/excel-pro...r-message.html ). Hopefully, this is the last question related to this project, and it should be the last question.

I have date format 1/24/2022 in ShtEarningCalendar.Cells(2, 9) , yes the date appears as 1/24/2022

Now I want to have Hyperlink string as below:
Hyperlink = """https://finance.yahoo.com/calendar/earnings?day=2022-01-24"""

But I cannot write above code directly, I need to get date from cell ShtEarningCalendar.Cells(2, 9)

Question: How can I rewrite the code for Hyperlink? It should be something like this --- Hyperlink = """https://finance.yahoo.com/calendar/earnings?day="" & ShtEarningCalendar.Cells(2, 9).Value This is just the idea, the cell value needs to be converted to yyyy-mm-dd.

Thanks.

Edit: I tried Hyperlink = """https://finance.yahoo.com/calendar/earnings?day=" & Format(ShtEarningCalendar.Cells(2, 9).Value, "yyyy-mm-dd") & "" , but it does not work.

Edit 2: I made a simple sample file (rather than uploading whole-project file), now I need to incorporate ActiveSheet.Cells(2, 9).Value into the code.