Converting multiple date serial number in same cell

[crtk09]

Hi all,

I've been given a sheet with approximately 25 cells that have 2 dates in each cell as serial numbers, and I'm trying to figure out how to quickly convert them to short/long dates.

Right now I'm doing it manually, where I copy-paste the serial into a date formatted cell, which returns the date, and then copy-paste that result back. I'm wondering if there's a smarter or more automated way I can accomplish this, especially considering there's a shape (arrow) involved as well.

E.g.:

Many thanks!

 

[Rick Rothstein]

Assuming your values start in cell A1, put this formula in cell B1 and copy it down to the end of your data. Next copy those formula results and Paste Special Values over top of the original values.

=REPLACE(REPLACE(A1,LEN(A1)-4,5,TEXT(RIGHT(A1,5),"m/d/yyyy")),1,5,TEXT(LEFT(A1,5),"m/d/yyyy"))

 

[crtk09]

Assuming your values start in cell A1, put this formula in cell B1 and copy it down to the end of your data. Next copy those formula results and Paste Special Values over top of the original values.

=REPLACE(REPLACE(A1,LEN(A1)-4,5,TEXT(RIGHT(A1,5),"m/d/yyyy")),1,5,TEXT(LEFT(A1,5),"m/d/yyyy"))

Thank you so much, Rick! This works perfectly and will save me a lot of time!