• 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 numbers to date

M

msquared99

Member
Here is the deal, I receive a file where the date is a number like this 112015 where I need it to be 01/01/2015 or at least 01/01/15. Also, dates can be 10252014 which is 10/25/2014.

How in the world do you convert this with varying lengths?

I have looked but cannot find anything to address this.

Thanks for your help!
 
Hi ,

How would you convert a number such as :

1112014

Would it be November 1 , 2014 or would it be January 11 , 2014 ?

Narayan
 
Good question. The only way I would know the answer to your question is that the files come in batches for each month. So, if the date were 1112015, and I received the file today then the date would be January 1, 2015. If we fast forwarded to November 2015 and I received the file, then the date would be November 1, 2015. Make sense?
 
=DATE(RIGHT(A1,4),LEFT(A1,LEN(MONTH(TODAY()))),MID(A1,LEN(MONTH(TODAY()))+1,LEN(A1)-LEN(MONTH(TODAY()))-4))
 
dEEPAK,

Thanks a lot!

I am assuming the TODAY portion will take care of when I enter say November and have the number 1112015.
 
upload_2015-1-17_10-58-49.png

=DATE(RIGHT(A1,4),LEFT(A1,LEN(MONTH($E$1))),MID(A1,LEN(MONTH($E$1))+1,LEN(A1)-LEN(MONTH($E$1))-4))
 
Hi Deepak,

It does not solve exactly with the following values,

112014 (file received on Jan) output: 01-01-2014 - OK
212014 (file received on Jan) output: 01-02-2014 - NOT OK
1112014 (file received on Jan) output: 11-01-2014- OK
 
Hi,

you can also use this:
=IF(LEN(A1)=6,DATEVALUE(LEFT(A1,1)&"/"&RIGHT(A1,4)),DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)))

Regards,
 
bijnas said:
Hi Deepak,

It does not solve exactly with the following values,

112014 (file received on Jan) output: 01-01-2014 - OK
212014 (file received on Jan) output: 01-02-2014 - NOT OK
1112014 (file received on Jan) output: 11-01-2014- OK
Click to expand...

If the file received in Jan then 212014 would be 122014 & then
result will : 02-01-2014
 
Deepak said:
Hi,

What to say for this..

View attachment 14857
It's wrong output.
Click to expand...

Ops..
Thanks for input Deepak.

Can you confirm this:
=IF(LEN(A1)<=7,DATE(RIGHT(A1,4),IF(LEN(A1)=6,LEFT(A1,LEN(A1)-5),LEFT(A1,LEN(A1)-6)),IF(LEN(A1)=6,MID(A1,2,1),MID(A1,2,2))),DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)))

Works also with 12302014 (30-Dec-2014)

Regards,
 
You must log in or register to reply here.
Back
Top