Date Text Conversation

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,265
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,430
Office Version
  1. 365
Platform
  1. Windows
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!
:)
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,033
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 28, 2009
Messages
2,265
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,265
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 28, 2009
Messages
2,265
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How does this part of the code work with repeats?

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

thank you
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,234
Office Version
  1. 2013
Platform
  1. Windows
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:
1626396672211.png


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


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:
1626396599820.png
 

Attachments

  • 1626396265719.png
    1626396265719.png
    3.9 KB · Views: 0
  • 1626396385423.png
    1626396385423.png
    3.1 KB · Views: 0

Forum statistics

Threads
1,175,502
Messages
5,897,809
Members
434,677
Latest member
Aurelied

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top