Converting different versions of Date-Time field

[Bullflip]

Hello,

I am trying to convert a date-time field to a previous version of it. I have thousands of records of one date time format and then it recently got changed to a different format.

Here's the original date-time that I was receiving:

="2021-12-03 07:19 AM"

And now I'm getting it like this:

Mon, Dec 06, 2021 10:31 AM

Is there a formula that I can use to easily convert the newer date-time entry to the previous one? Any help would be greatly appreciated. Thank you!

 

[etaf]

if they are real dates , then it should be a formatting
so if you right click on the cell and change to General
do you just get a number
If so then a format will work

If not then you will need to use a combination of MID/Left/Right functions to pull the date into a new column

 

[Bullflip]

if they are real dates , then it should be a formatting
so if you right click on the cell and change to General
do you just get a number
If so then a format will work

If not then you will need to use a combination of MID/Left/Right functions to pull the date into a new column

I think I will have to use a combination of the MID/Left/Right functions to separate the date. Thanks for the reply!

 

[etaf]

try
=DATEVALUE(MID(A1,10,2)&"/"&MID(A1,6,3)&MID(A1,14,4))+TIMEVALUE(RIGHT(A1,8))

should change to a real date and time value

Now you can format
using custom format
yyyy-mm-dd h:mm AM/PM

Book1
	A	B
1	Mon, Dec 06, 2021 10:31 AM	2021-12-06 10:31 am
Sheet1

Cell Formulas
Range	Formula
B1	B1	=DATEVALUE(MID(A1,10,2)&"/"&MID(A1,6,3)&MID(A1,14,4))+TIMEVALUE(RIGHT(A1,8))

 

[Bullflip]

This worked great! Thank you for your help

 

[etaf]

you are welcome