Format Dates as Dates 1

[perrymans]

Is there a way to take a column that is full of differently formatted dates and format them as date?

I keep getting conversion errors that want to delete a little over 100 of the records.

Formats include:

40/10/24
8/7/1924
24NOV48

Thanks. Sean.

 

[Remou]

24NOV48 is not a valid date by Access standards, so you will not be able to convert it. Furthermore, if the field is a text field, it is possible that day, month, year and in an order that could cause confusion. I do not think that this is a task that can easily be fully automated.

 

[perrymans]

Thanks Remou.

How about this:

19/2/28 is turned into 2/28/2019.

How can I update all years >2000 to 19@@?

So the date would be 2/28/1919.

Thanks. Sean.

 

[Remou]

How will you decide which it is: yy/mm/dd, dd/mm/yy or mm/dd/yy?

 

[perrymans]

By eyeballing for patterns around certain times.

Thanks. Sean.

 

[Remou]

In that case, I suggest you simply copy all dates to a date time field and manually check.

 

[perrymans]

I did, the blanks left after the errors were the easiest way to identify the problems.

All good now though.

Sometimes manual is easier!


Thanks. Sean.