Date function with 5 and 6 digit dates - changing to 1900's - SOLVED

Hi Guys.

I have recently started receiving files containing a large amount of information. These files come in .txt format and it is up to me to seperate the data out

The part I am having difficulty with is the date. It is included in a long string of data that I seperate out using text to columns. All good there.

If the date is 1st to the 9th of the month it comes out as for example 71014 If the date is double digit days then it comes out as 241014

I am unable to convert this to a date using the format cell option, it comes out badly. I have used the date function =DATE(RIGHT(C5,2), MID(C5, 3,2), LEFT(C5,2))

This works in a way until I get to the year, there it forces the year as 19. so a date of 24/10/2014 shows as 24/10/1914

For the 5 digit dates I have formatted to custom and put in 6 zeroes to force it to be a 6 digit date, this has not gone according to plan for example

Original 40812
formatted to 040812
Date function displays: 10/10/1918

Any suggestions on this?

Thanks

Hi,

Assuming you have typical UK regional settings, this should suffice I think
=TEXT(C5,"00-00-00")+0
then format the cell as a Date.

You're welcome.