identify date format in R before converting

Question

I have a simple data set which has a date column and a value column. I noticed that the date sometimes comes in as mmddyy (%m/%d/%y) format and other times in mmddYYYY (%m/%d/%Y) format. What is the best way to standardize the dates so that i can do other calculations without this formatting causing issues?

I tried the answers provided here Changing date format in R

and here How to change multiple Date formats in same column

Neither of these were able to fix the problem.

Below is a sample of the data

Date, Market
12/17/09,1.703
12/18/09,1.700
12/21/09,1.700
12/22/09,1.590
12/23/2009,1.568
12/24/2009,1.520
12/28/2009,1.500
12/29/2009,1.450
12/30/2009,1.450
12/31/2009,1.450
1/4/2010,1.440

When i read it into a new vector using something like this

dt <- as.Date(inp$Date, format="%m/%d/%y")

I get the following output for the above segment

dt                  Market
2009-12-17          1.703
2009-12-18          1.700
2009-12-21          1.700
2009-12-22          1.590
2020-12-23          1.568
2020-12-24          1.520
2020-12-28          1.500
2020-12-29          1.450
2020-12-30          1.450
2020-12-31          1.450
2020-01-04          1.440

As you can see we skipped from 2009 to 2020 at 12/23 because of change in formatting. Any help is appreciated. Thanks.

Answer 1

> dat$Date <- gsub("[0-9]{2}([0-9]{2})$", "\\1", dat$Date)                                                                                                                                                                                  
> dat$Date <- as.Date(dat$Date, format = "%m/%d/%y")                                                                                                                                                                                        
> dat                                                                                                                                                                                                                                       
          Date Market                                                                                                                                                                                                                         
# 1  2009-12-17  1.703                                                                                                                                                                                                                         
# 2  2009-12-18  1.700                                                                                                                                                                                                                         
# 3  2009-12-21  1.700                                                                                                                                                                                                                         
# 4  2009-12-22  1.590                                                                                                                                                                                                                         
# 5  2009-12-23  1.568                                                                                                                                                                                                                         
# 6  2009-12-24  1.520                                                                                                                                                                                                                         
# 7  2009-12-28  1.500                                                                                                                                                                                                                         
# 8  2009-12-29  1.450                                                                                                                                                                                                                         
# 9  2009-12-30  1.450                                                                                                                                                                                                                         
# 10 2009-12-31  1.450                                                                                                                                                                                                                         
# 11 2010-01-04  1.440