Date formatting

[Welsh Mark3]

I am based in the US but am using a file that originates in the UK (I believe). As you will see from the table below they all appear to be dates but when a formula is used the data doesn't act like a date.

I have tried to used format cell function and change to a US format date, with no luck.

Has anyone else seen this before is there a way to correct this issue?

DATE	Month	Formula Used in Column B
17/01/2018 <tbody> </tbody>	17/01/2018 <tbody> </tbody>	=TEXT(b2 ,"mmmm")
11/01/2018 <tbody> </tbody>	November <tbody> </tbody>	=TEXT(b3 ,"mmmm")
31/01/2018 <tbody> </tbody>	31/01/2018 <tbody> </tbody>	=TEXT(b4 ,"mmmm")
03/01/2018 <tbody> </tbody>	March <tbody> </tbody>	=TEXT(b5 ,"mmmm")

<tbody>
</tbody>

 

[Joe4]

That is because the ones it can convert are coming over as dates, and the others are coming over as text (the left-justified ones).
The following formula will work for either case, for an entry in cell A2:

=IF(ISNUMBER(A2),TEXT(A2,"mmmm"),TEXT(DATE(YEAR(TODAY()),MID(A2,4,2),1),"mmmm"))

 

[Welsh Mark3]

That is because the ones it can convert are coming over as dates, and the others are coming over as text (the left-justified ones).
The following formula will work for either case, for an entry in cell A2:

=IF(ISNUMBER(A2),TEXT(A2,"mmmm"),TEXT(DATE(YEAR(TODAY()),MID(A2,4,2),1),"mmmm"))

This formula allows we to change it to a month (text) which is helpful but I was looking to do is to change the exact date, so they should all be january.

 

[Joe4]

Sorry, your original post confused me. It looks like you were trying to pull the month from the formulas you posted.
The following formula will convert all entries to valid dates:

=IF(ISNUMBER(A2),A2,DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)))

Make sure to format the results in a Date format, or else you may see the numerical representation of the date.

 

[Welsh Mark3]

Sorry, your original post confused me. It looks like you were trying to pull the month from the formulas you posted.
The following formula will convert all entries to valid dates:

=IF(ISNUMBER(A2),A2,DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)))

Make sure to format the results in a Date format, or else you may see the numerical representation of the date.

Apologies for the confusion and thank you

 

[Joe4]

You are welcome.