What formula do I use to get a date value from another cell returned?

[StacyAnn1974]

I'm working on a spreadsheet for planning. Across the top row are the dates. The first cell is a drop down calendar and the columns just add +1 to get consecutive dates to 13 weeks.

If I put an x or anything in the rows below, I want it to return the dates of the first row like this 7/01, etc. I'm working Google Sheet and Excel both and haven't figured out the correct formula yet. I'm pretty new to Google Sheets.

 

[PeteWright]

@StacyAnn1974 Could you post an example* and upload an image with higher resolution (your's is barely visible).
When referring to cells, please post their absolute "address" like "A1", "Row 1" or "Column A" instead of "in the rows below"

*) Could you please upload two images, one showing your original sheet and another sheet that shows exactly the desired result.

 

[StacyAnn1974]

Let me know if that's better. Cell G4 is a dropdown calendar. H4:AF4 is just a +1 formula to get the continuing dates.
Is it possible to type any data like an “x” in cell G6, and have the date populate like 7/1 (not 1 and not 7/1/23)? It may be impossible but I don't have extensive knowledge of the formulas so I wanted to ask. Thanks!

 

[PeteWright]

Thanks for the update.

You need to know two basics:

1. A cell cannot contain both, an user entered value and a formula
2. A formula can only change the value of the cell it is in

As far as I know it's impossible to accomplish with pure Excel formulas.
You may need VBA for Excel that changes the cells' value on user interaction, but I'm not sure if your version can handle that.

So would you please tell me what Excel version you want to work with and if VBA is an option.

 

[StacyAnn1974]

Excel for Mac
Version 16.71
Microsoft 365 Subscription

Thanks!