DATE formula

**svujke** · 10-18-2019, 06:18 AM

Hi,
I have following number from which I am extracting date, ie. 2302989870009. First two digits are for the day of the month, 3rd and 4th numbers are for the month, and 5th trough 7th are numbers for year, in this case 23/02/1989.
I am using following formula to extract date from cell A2: DATE(MID(A2,6,2),MID(A2,3,2),MID(A2,1,2). The formula works fine until the year reaches 2000 or higher, ie 2302000870009. In that case I am getting year 1900 instead 2000.
Is there any formula to solve this problem.
Thanks in advance for any help.
Regards
Sasa

**Special-K** · 10-18-2019, 06:27 AM

=(left(a1,2)&"/"&mid(a1,3,2)&"/"&if(mid(a1,5,1)>"0",1,2)&mid(a1,5,3))+0

What happens when the day is 1st - 9th ?
The input will drop the leading 0 I believe

**alansidman** · 10-18-2019, 06:46 AM

I was able to achieve this using Power Query/Get and Transform. I did use US date convention so that it would work on my system. You can adjust that. Here is the Mcode

Please Login or Register  to view this content.

and I have attached a file. Note that your data needs to be in text format for this to work.If you are unfamiliar with PQ, then check out the links in my signature .