Unifi two date formats where one is DATE and the other is TEXT

[pitaszek]

Hello everyone,

I've got data set which has two date formats where one is DATE and the other is TEXT. Ideally I would like VBA to convert TEXT to date. I appreciate any helpful tips! Sample format is as follows:

Shorter one is DATE, longer with timestamp is TEXT. Ideal ouput is eg. 11/01/2016 as a date format.
[TABLE="width: 117"]
<tbody>[TR]
[TD="align: right"]12/31/2016 23:59
[/TD]
[/TR]
[TR]
[TD="align: right"]11/01/2016
[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2016 23:59
[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2016 23:59
[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2016 23:59
[/TD]
[/TR]
[TR]
[TD="align: right"]09/30/2016 23:59
[/TD]
[/TR]
[TR]
[TD="align: right"]10/17/2016 23:59
[/TD]
[/TR]
[TR]
[TD="align: right"]11/15/2016 23:59
[/TD]
[/TR]
[TR]
[TD="align: right"]09/30/2016 23:59
[/TD]
[/TR]
[TR]
[TD="align: right"]11/01/2016
[/TD]
[/TR]
[TR]
[TD="align: right"]11/01/2016
[/TD]
[/TR]
[TR]
[TD="align: right"]09/30/2016 23:59
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much dear community,
W

 

[arthurbr]

Are the date and timestamps in the same cell?

 

[pitaszek]

Yes. Column sharnk and wrapped it to the next line.

W

 

[BGY23]

Hi,

I don't know about a macro but = Value(cell with date in) will convert it to a number and then format the the cell as date. It works with both your date types

 

[pitaszek]

Thanks for your contribution BGY23. This solution is good, but I feel like new dates do not behave like actual dates. If you try to filter on dates you will see that they would group into Months and Years automatically.

 

[arthurbr]

I feel like new dates do not behave like actual dates.

What do you mean?

 

[pitaszek]

If you filter by date and which have proper date format they would be seen in the filter as grouped for years and months while created using VALUE and TEXT, DATE functions as BGY23 proposes do not.

 

[MARK858]

Are you sure that they aren't all dates but from a region that has it's date format as mm/dd/yyyy and yours is dd/mm/yyyy which means it will be text to you if it is past the 12th of the month?