Replacing Unique Identifier With Dates

[RossSmfc93]

Hi

I have hundreds of spreadsheets where I need to convert numbers to dates. For example, in the photograph, I need to convert number 43 to todays date (18 May 2022). The days count back so number 42 will be yesterdays date (17 May 2022). I have been working on this by using find and replace to replace all the relevant numbers to date but takes a huge amount of time. Is anyone aware of any alternative?

IMG_1810.jpeg]

I am having issues attaching my specimen spreadsheet so apologies hopefully the picture will suffice.
*
Many thanks

[MrShorty]

Are you required to replace the values in column A, or would a helper column with the date be sufficient? I would use a formula like =TODAY()-(43-A4077) in an adjacent empty column, copy/paste/fill the formula up/down as far as needed. Now I have a helper column with the desired date.

If you decide you must replace the values in column A, it is a simple copy/paste special/as values and number formats operation to overwrite the values in column A with the new dates.

Will that work for you, or is there some restriction preventing you from adding the helper column?

[dosydos]

If you want to overwrite data then you will have to use VBA. Otherwise you are going to need to utilize a helper column.
I inserted a new column to the right of day. I then utilized a vlookup() function to go after a table i created over in columns O:P.
I set the table so that 43 = 5/18/2022. You could always use the TODAY() function instead if 43 needs to always equa; whatever the day you are working on its date is. Then i just did a simple =-1 function below so it subtracted one day from the original date. Not sure if this is what you wanted. Like i said earlier though, if you dont want helper columns, and you don't want to continue to use FIND & REPLACE, then you will need to use VBA.