Convert Text Date to Dates

hi

I need a formula to convert the below text dates to Date Format.

26/05/2020 02:58:14 PM to 5/26/2020 02:58:14 PM

If you want to keep the word "to" between those dates then they will be in text format. You will need to have two separate columns, one for the start date/time and the other for the finish date time.

Hope this helps.

Pete

Custom format m/d/yyyy h:mm:ss AM/PM

Sorry for the confusion,

This is the text date I have 26/05/2020 02:58:14 PM

I need to convert result to this 5/26/2020 02:58:14 PM

Before giving you a solution, let's make sure it is the right solution. In this instance, one example does not convey the entire possible problem. Do you think you have any dates that are "correct" in your list? In other words, do you have a column of real dates and text dates and are asking us to make only the text dates into real dates?

@Rick

I have only one column (E) which is text date in this format dd/mm/yyyy hh:mm:ss AM/PM.

In the next column (F) I need a formula to convert to real date format including time.

Okay, I just wanted to make sure you had all text values and no "real" date values as that would have been a major problem. One more question... you text dates where the day number is less than 10, does it always have a leading zero or not? In other words for the date February 7, 2020, would your text date look like this...

07/02/2020

or this...

7/02/2020

@Rick

this is the format for less than 10, 01/06/2020 01:12:58 PM
dd/mm/yyyy hh:mm:ss AM/PM

Okay, good, that makes the formula easier. Give this formula a try...

Formula:

Please Login or Register  to view this content.

Note: You will probably have to Custom Format the cell you put the formula in to make it show both the date and time.

@Rick,

Thanks it's working, Noted...