I need a formula to convert text into a date format.

[maxim642]

Below is an example of my date format (UK).

22/01/2021 2:46:39 PM
6/05/2021 12:05:51 PM

In the adjacent cells, I would like to see 22/01/21 & 06/05/21, respectively. Thanks.

I tried using the LEFT formula, but wasn't sure how to format it as a date within that formula.

 

[jasonb75]

but wasn't sure how to format it as a date within that formula.

=LEFT(A1,10)+0 then format the cell as date.

Note that this assumes dd/mm/yyyy regional system settings. With other settings you will get errors or wrong dates.

 

[Joe4]

Are those values entered as Text or Date/Time?

If you aren't sure, try using the ISNUMBER function on them, i.e.

=ISNUMBER(A1)

If it returns TRUE, it is a valid date/time entry.
If it returns FALSE, it is a text entry.

Jason's formula will work on text entries only.

If it is a Date/Time entry, you can simply use:

=INT(A1)

and choose your desired date format.

 

[maxim642]

=LEFT(A1,10)+0 then format the cell as date.

Note that this assumes dd/mm/yyyy regional system settings. With other settings you will get errors or wrong dates.

Thanks, this worked!

 

[maxim642]

Are those values entered as Text or Date/Time?

If you aren't sure, try using the ISNUMBER function on them, i.e.

=ISNUMBER(A1)

If it returns TRUE, it is a valid date/time entry.
If it returns FALSE, it is a text entry.

Jason's formula will work on text entries only.

If it is a Date/Time entry, you can simply use:

=INT(A1)

and choose your desired date format.

Cheers, Joe. I checked using your suggestion, and it did indeed return FALSE.

 

[Joe4]

Cheers, Joe. I checked using your suggestion, and it did indeed return FALSE.

OK, then Jason's formula is the way to go!

Note the reason why that works is because dates and times are actually stored in Excel as numbers, specifically the number of days since 1/0/1900 (so time is just a fraction of a day).
(That is why if you enter a date in any cell, and change the format to "General", you see a large number like 44000).

My reply addresses if you had a valid date/time entry, and wanted just the date portion.
We can use the INT function to take just the integer portion and drop the decimal (which is the time portion of that entry).

 

[jasonb75]

@Joe4
Sometimes you can see the difference from the example. If you look at the second date the format is unconventional, suggesting that it is either text from an external source or a very specific custom format.

While not impossible, in my view it is extremely unlikely that anyone with the knowledge to set that as a custom format with a proper numeric date would need to come to the forum to ask how to separate the date and time.

 

[Joe4]

Sometimes you can see the difference from the example. If you look at the second date the format is unconventional, suggesting that it is either text from an external source or a very specific custom format.

True, though a format that starts like "d/mm/yyyy..." would return that.

While not impossible, in my view it is extremely unlikely that anyone with the knowledge to set that as a custom format with a proper numeric date would need to come to the forum to ask how to separate the date and time.

You would think so, but I have actually seen that question asked in the past.

I usually try not to assume anything!

Note that my original reply was not a refute of your original reply. I had originally just asked the question about the format, and amended it after I posted my reply and saw that you had submitted a reply while I was working on it. So I amended my original post to clarify the differences.

 

[jasonb75]

Note that my original reply was not a refute of your original reply.

I didn't think that it was, Joe. My apologies if that is how my response came across. I know I make mistakes with my suggestions at times, we all have bad days and I would rather somebody challenge what I've said than let an error slip through and cause problems for those we're trying to help.

True, though a format that starts like "d/mm/yyyy..." would return that.

This was what I was referring to by a 'very specific custom format'. Maybe it is variable with different regional settings, but none of the native formats with UK settings mix a single digit day with a double digit month. It would have to be set up as a custom format by the user. With just the date, I would say that it is nothing elaborate, but with the time format, I've seen experienced users that don't know how to set the AM/PM part correctly.

 

[Joe4]

You are correct that based on the samples they posted, that it is probably more than likely a text entry. Truth be told, I didn't notice that "strange" format at first glance until you mentioned it.
I have seen people make typos in samples/examples many times, so you cannot always trust the format of the samples they posted, but this seemed to be a pasted image, so that probably is unlikely too.
So the assumptions you made appeared to be "more than likely correct" in this instance.

In any event, I also often like to use these type of questions as teaching mechanisms as well, as many people (especially noobies) don't realize that Excel actually stores dates/times as numbers.
So it can be an educational opportunity too! It might help them at some point down the road.