• 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

A

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.
 
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.
 
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.
 
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.
 
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))
 
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
 
vletm said:
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.
Click to expand...


Thank you. I actually used the suggestion for test to column resolution someone suggested and it worked.
 
Chihiro said:
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))
Click to expand...


Thank you so much for your assistance. Love this forum. Replies are always speedy and helpful.
 
You must log in or register to reply here.
Back
Top