# Converting different versions of Date-Time field

#### Bullflip

##### New Member
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!

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

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!

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
AB
1Mon, Dec 06, 2021 10:31 AM2021-12-06 10:31 am
Sheet1
Cell Formulas
RangeFormula
B1B1=DATEVALUE(MID(A1,10,2)&"/"&MID(A1,6,3)&MID(A1,14,4))+TIMEVALUE(RIGHT(A1,8))

This worked great! Thank you for your help

you are welcome

Replies
4
Views
121
Replies
8
Views
78
Replies
1
Views
181
Replies
11
Views
456
Replies
2
Views
155

1,203,323
Messages
6,054,724
Members
444,747
Latest member
Jaborsum

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back