• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert general text to date format

Aspur

New Member
I am trying to convert general text to a date . the raw data that gets pulled in to an excel worksheet looks like this 50119 and I'm trying to find the formula in excel to convert that to 05/01/2019. I've tried =date, =datevalue and I'm not having any luck except to get some while date in march of 2037. Any assistance will be apprecited.
 

vletm

Excel Ninja
Aspur
Activate Your 'looks like cell' as below and see ... what is there?
59917
Can You get something like above left side of that cell?
If ... Yes ... then follow its instructions.
If still challenge, then upload here sample of Your file.
 

Aspur

New Member
I am not sure what you mean by activate "looks like cell". I don't get anything with an apostrophe before the number I have attached the raw file. the data is dumped from the system of record as an *.xls file and sent via outlook if this helps any.
 

vletm

Excel Ninja
Aspur
activate "looks like cell"
1) use Your mouse - okay?
2) select Your cell which looks like 50119 as in my sent snapshot - okay?
3) if You didn't get something like that snapshot
... then as I wrote ... still challenge, then upload here sample of Your file here.
Actually, I don't need that file - but someway, You need to show Your challenge ... which has "looks like" case.
 

Chihiro

Excel Ninja
Assuming it's always length of 5 or 6 (i.e. ddyy for last 4 character)... something like below.
=DATE(2000+RIGHT(A1,2),--SUBSTITUTE(A1,RIGHT(A1,4),""),--LEFT(RIGHT(A1,4),2))
 

bosco_yip

Excel Ninja
Another formula way,

A1: 50119

B1, enter formula :

=0+TEXT(A1,"00\/00\/00")

then, format B1 to Date and choose type in suit as you need

Regards
Bosco
 

Aspur

New Member
Aspur
activate "looks like cell"
1) use Your mouse - okay?
2) select Your cell which looks like 50119 as in my sent snapshot - okay?
3) if You didn't get something like that snapshot
... then as I wrote ... still challenge, then upload here sample of Your file here.
Actually, I don't need that file - but someway, You need to show Your challenge ... which has "looks like" case.

Thank you. I actually used the suggestion for test to column resolution someone suggested and it worked.
 

Aspur

New Member
Another formula way,

A1: 50119

B1, enter formula :

=0+TEXT(A1,"00\/00\/00")

then, format B1 to Date and choose type in suit as you need

Regards
Bosco

THank you for the reply. I will keep this in my notes of excel forumlas.
 

Aspur

New Member
Assuming it's always length of 5 or 6 (i.e. ddyy for last 4 character)... something like below.
=DATE(2000+RIGHT(A1,2),--SUBSTITUTE(A1,RIGHT(A1,4),""),--LEFT(RIGHT(A1,4),2))

Thank you so much for your assistance. Love this forum. Replies are always speedy and helpful.
 
Top