Using a Formula to Copy a Date and Change the Format

[u4carson]

This is an odd situation. I know how to fix this without a formula, but for my purposes I need the data to be copied from one cell to another cell using a formula without amending the cells manually in any way.

The problem is that the original cell is not recognizing the date as a date so the copied cell is not either. What I need to do is to write a formula that copies the date into a new field where the new field actually recognizes it as a date.

CELL A2
01/02/2016 (DOES NOT RECOGNIZE AS DATE)

COPY TO CELL B2
01/02/2016 (DOES RECOGNIZE AS DATE)

Any help would be greatly appreciated, I can't seem to figure this one out.

 

[Joe4]

You can just use the DateValue function in cell B2, i.e.
=DateValue(A2)
and choose your desired date format

Note that if your sole purpose is to convert the entries to valid dates, you convert all your entries in column A to a date at once without using column B by applying Text to Columns to Column A.

 

[u4carson]

Well this is embarrassing, I was using =DATE(A2) and not =DATEVALUE(A2) ... that fixed it... thanks!

 

[Joe4]

You are welcome.

Yes, the Date function is used when you are building the date by supplying the year, month, and day in separate arguments.
See: https://www.techonthenet.com/excel/formulas/date.php