Convert mixed Date formats into one Date format.

[Inacio11]

Dear all
I have a file that is automatic filled-out by 3rd-parties. This file contains a column containing dates that are displayed in mixed formats (mm/dd/yyyy) and (dd/mm/yyyy) and I wish to display them all in just one format (dd/mm/yyyy) – (brazilian).

Any formula that will help me convert all the dates to one format ?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]mixed[/TD]
[TD](Brazil) DD/MM/YYYY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109"]Format received[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl65, width: 138"]Correct format[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]NOK[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109"]08/12/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl65, width: 138"]12/08/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]NOK
[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109"]08/12/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12/08/2018[/TD]
[/TR]
[TR]
[TD]NOK[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109"]08/12/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12/08/2018[/TD]
[/TR]
[TR]
[TD]OK[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109"]14/08/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109"]14/08/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]OK[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109"]14/08/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109"]14/08/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]OK[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109"]15/08/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]15/08/2018[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance !
Regards,
Inacio

 

[Joe4]

If the really are entered as dates, and the dates are entered correctly, it should just be a matter of selecting your entire range and changing the Date format to: dd/mm/yyyy

If it is not strictly a formatting issue, and if the dates are not entered correctly, that is going to be a challenge.
For example, if you have an entry like "12/08/2018", how can you tell by looking at it if it should be "12/08/2018" or "08/12/2018"?
How do you know which one is right? They are both valid dates in both formats.

 

[Inacio11]

Hi Joe4, thak you for your repply !
Concerning to your question about which date should be valid...the current date could be used as a parameter since the dates are fill-out daily !
Regards,

 

[Joe4]

Concerning to your question about which date should be valid...the current date could be used as a parameter since the dates are fill-out daily !

Is the current date listed somewhere on the sheet so that we can see the format it is in?
You also said that you have mixed formats, so if that is the case, then I don't think that would work in determining what the actual date should be.

Unless... there is more information that may be helpful.
In your example, it looks like the data may be sorted by date (which would make sense if they are filled out daily).
If that is the case, we may be able to do something with that, if you can provide a little more detail:
- are the dates listed always in the past?
- how far back will the dates go (compared to today's date)?
- are the dates always in increasing order (like your example lists)?
- like I mentioned in my previous post, does changing the format of the column to dd/mm/yyyy fix the issue (if not, we have incorrect entries that need correcting)?

 

[Inacio11]

Hi Joe4

Yeah ! Looks very complicate to solve it as it is.
However, at the same file I found a column that have date & time as follow: 2018-09-07T23:01:33.832Z
Any idea about which formula I could use to extract just the date on format dd/mm/yyy
Regards,
Jeferson

 

[Joe4]

However, at the same file I found a column that have date & time as follow: 2018-09-07T23:01:33.832Z
Any idea about which formula I could use to extract just the date on format dd/mm/yyy

If the value is in cell A1, you can use this to convert it to a date, and then format the cell in any date format you desire.

=DATEVALUE(LEFT(A1,10))