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?

[TABLE="width: 500"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]Month[/TD]
[TD]Formula Used in Column B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 205"]
<tbody>[TR]
[TD="class: xl63, width: 205"]17/01/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]17/01/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=TEXT(b2
,"mmmm")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 205"]
<tbody>[TR]
[TD="class: xl65, width: 205, align: right"]11/01/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]November[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=TEXT(b3
,"mmmm")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 205"]
<tbody>[TR]
[TD="class: xl65, width: 205"]31/01/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]31/01/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=TEXT(b4
,"mmmm")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 205"]
<tbody>[TR]
[TD="class: xl65, width: 205, align: right"]03/01/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]March[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=TEXT(b5
,"mmmm")[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.