Date Text Conversation

[mahmed1]

Hiya

i have a column of data (column A) that has text values like this

6/14/21 12:00 AM
6/14/21 01:00 AM etc

i have tried to do a text to column - did a find replace - did + 0 to try and make it into a date but nothings working

what i want to do is just get the date in dd/mm/yy format

eg 14/06/21

can someone please help me get around this annoying problem

thank you

 

[Joe4]

It is probably a problem that the OP is having

You may be right, they may be more likely to have the same regional settings as you than they do to me.
In any event, they have different options which hopefully cover all the bases!

 

[Rick Rothstein]

One thing the OP has to watch out for is if his column of dates end up with both "real" dates and text dates. If the OP has this, then none of his "real" dates are correct (unless the day and month number are the same). The reason is Excel will have swapped the day and month numbers from what the OP thinks they should be. For example, if the "real" date is, say, 7/11/2021, I am sure the OP would have wanted that to be 11/7/2021. So simply converting the text dates would not be sufficient.

 

[mahmed1]

Thank you all so so much
Yep i tried everything but as it was stored as text it would just not let me change to dates

yes the value comes out in
Month/Day/Year but id want in Day/Month/Year

sometimes though the day or month can be in 06, 07 etc but other times just 6, 7 etc

 

[DanteAmor]

sometimes though the day or month can be in 06, 07 etc but other times just 6, 7 etc

Did you try my formula from post # 5?

 

[mahmed1]

Ive not tried yet as i didnt have access to a computer but will give it a go..i was just trying to respond back to some of the comments

I appreciate all your guys help

 

[mahmed1]

How does this part of the code work with repeats?

TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",99)),99,99)))

thank you

 

[DanteAmor]

How does this part of the code work with repeats?
TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",99)),99,99)))

It works like this

SUBSTITUTE(A1,"/",REPT(" ",99))
Replace "/" with 99 spaces, it would look like this:

MID(SUBSTITUTE(A1,"/",REPT(" ",99)),99,99))
Extracts the data, starting at position 99, taking 99 characters.it would look like this:

TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",99)),99,99)))
Remove all spaces from the front of the text and from the back of the text, it would look like this: