-3

I have a data set with 3 date columns whose date columns are not in proper format and some also contain time which I am not able to remove I want to remove time from my date columns and then convert them into mm/dd/yyyy format.

df<- structure(list(Date.1 = structure(c(161L, 165L, 23L, 165L, 162L, 27L, 168L, 165L, 27L, 15L, 17L, 18L, 22L, 19L, 21L, 29L, 30L, 29L, 32L, 34L, 35L, 35L, 39L, 39L, 46L, 40L, 43L, 2L, 4L, 6L, 8L, 7L, 11L, 9L, 80L, 83L, 59L, 59L, 67L, 67L, 67L, 92L, 92L, 107L, 100L, 107L, 113L, 128L, 117L, 128L, 123L, 123L, 128L, 144L, 137L, 136L, 151L, 42L, 53L, 64L, 71L, 71L, 101L, 87L, 87L, 111L, 111L, 10L, 81L, 81L, 125L, 152L, 125L, 125L, 156L, 125L, 47L, 47L, 55L, 65L, 61L, 65L, 65L, 72L, 25L, 72L, 102L, 102L, 93L, 65L, 118L, 98L, 93L, 102L, 65L, 93L, 118L, 96L, 99L, 104L, 99L, 116L, 116L, 66L, 66L, 90L, 169L, 94L, 97L, 109L, 70L, 119L, 73L, 13L, 84L, 153L, 146L, 153L, 146L, 51L, 51L, 158L, 153L, 56L, 88L, 62L, 103L, 103L, 112L, 112L, 112L, 139L, 141L, 141L, 145L, 148L, 148L, 143L, 149L, 150L, 142L, 106L, 69L, 86L, 114L, 57L, 115L, 140L, 63L, 89L, 157L, 95L, 58L, 91L, 105L, 110L, 154L, 155L, 160L, 167L, 16L, 28L, 38L, 31L, 33L, 37L, 41L, 82L, 76L, 127L, 120L, 121L, 124L, 129L, 131L, 135L, 138L, 147L, 85L, 54L, 126L, 26L, 159L, 44L, 78L, 122L, 141L, 1L, 14L, 52L, 60L, 24L, 108L, 132L, 140L, 68L, 170L, 50L, 74L, 95L, 75L, 155L, 171L, 164L, 163L, 166L, 20L, 36L, 49L, 45L, 48L, 5L, 3L, 12L, 77L, 79L, 130L, 133L, 134L), .Label = c("1/1/2019", "1/11/2019", "1/16/2020", "1/17/2019", "1/2/2020", "1/21/2019", "1/24/2019", "1/25/2019", "1/27/2019", "1/3/201912:00:00AM", "1/30/2019", "1/30/2020", "1/6/201912:00:00AM", "10/1/2019", "10/10/2018", "10/11/2019", "10/17/2018", "10/18/2018", "10/20/2018", "10/20/2019", "10/22/2018", "10/25/2018", "10/3/2018", "10/3/2019", "10/3/201912:00:00AM", "10/7/2019", "10/8/2018", "11/1/2019", "11/17/2018", "11/19/2018", "11/19/2019", "11/20/2018", "11/20/2019", "11/21/2018", "11/26/2018", "11/28/2019", "11/29/2019", "11/8/2019", "12/1/2018", "12/16/2018", "12/18/2019", "12/2/201912:00:00AM", "12/22/2018", "12/23/2019", "12/24/2019", "12/3/2018", "12/3/201912:00:00AM", "12/30/2019", "12/5/2019", "12/6/2019", "12/6/201912:00:00AM", "13/02/19", "13/02/201900:00", "13/03/19", "13/03/201900:00", "13/03/202000:00", "13/04/19", "13/07/19", "14/01/201900:00", "14/02/19", "14/03/201900:00", "15/03/202000:00", "15/05/19", "16/02/201900:00", "16/03/201900:00", "16/05/201900:00", "17/01/201900:00", "17/05/19", "18/02/19", "18/05/201900:00", "19/02/201900:00", "19/03/201900:00", "19/05/201900:00", "19/06/19", "19/07/19", "2/14/2020", "2/17/2020", "2/2/2020", "2/20/2020", "2/3/2019", "2/3/201912:00:00AM", "2/5/2020", "2/6/2019", "2/6/201912:00:00AM", "20/01/19", "20/02/19", "20/02/201900:00", "20/03/202000:00", "20/05/19", "20/05/201900:00", "20/07/19", "21/01/201900:00", "21/03/201900:00", "21/05/201900:00", "21/06/19", "22/04/201900:00", "22/05/201900:00", "23/03/201900:00", "23/04/201900:00", "24/01/201900:00", "24/02/201900:00", "24/03/201900:00", "24/03/202000:00", "24/04/201900:00", "24/07/19", "25/01/19", "25/01/201900:00", "25/04/19", "25/05/201900:00", "25/07/19", "26/02/201900:00", "26/03/202000:00", "27/01/201900:00", "27/02/19", "27/03/19", "28/04/201900:00", "29/01/201900:00", "29/03/201900:00", "29/05/201900:00", "3/13/2020", "3/15/2020", "3/19/2020", "3/2/201912:00:00AM", "3/28/2020", "3/3/201912:00:00AM", "3/5/2019", "3/8/2020", "30/01/201900:00", "4/12/2020", "4/21/2020", "4/22/2020", "4/5/2019", "5/1/2020", "5/15/2020", "5/16/2020", "5/2/201912:00:00AM", "5/2/20196:00:00AM", "5/29/2020", "5/31/2020", "5/5/2019", "6/1/2020", "6/12/2020", "6/16/2020", "6/2/201912:00:00AM", "6/3/2020", "6/6/201912:00:00AM", "6/6/2020", "6/7/2020", "6/8/2020", "6/9/2020", "7/2/201912:00:00AM", "7/3/201912:00:00AM", "7/6/201912:00:00AM", "8/1/2019", "8/25/2019", "8/3/201912:00:00AM", "8/6/2019", "8/6/201912:00:00AM", "9/11/2019", "9/14/2019", "9/21/2018", "9/22/2018", "9/22/2019", "9/23/2019", "9/24/2018", "9/27/2019", "9/29/2019", "9/30/2018", "9/5/201912:00:00AM", "9/6/2019", "9/8/2019"), class = "factor"), Date.2 = structure(c(167L, 170L, 20L, 170L, 167L, 24L, 173L, 170L, 24L, 14L, 14L, 15L, 19L, 16L, 18L, 26L, 27L, 26L, 29L, 29L, 31L, 31L, 36L, 36L, 42L, 37L, 40L, 2L, 4L, 6L, 8L, 7L, 11L, 9L, 73L, 76L, 53L, 53L, 61L, 61L, 61L, 84L, 84L, 96L, 92L, 96L, 104L, 123L, 125L, 123L, 118L, 118L, 123L, 146L, 135L, 134L, 158L, 39L, 49L, 58L, 65L, 65L, 93L, 85L, 80L, 102L, 102L, 10L, 74L, 74L, 138L, 153L, 121L, 130L, 161L, 121L, 43L, 43L, 51L, 59L, 55L, 59L, 62L, 81L, 62L, 87L, 98L, 103L, 87L, 87L, 113L, 89L, 87L, 103L, 87L, 105L, 113L, 100L, 100L, 95L, 90L, 110L, 110L, 60L, 60L, 82L, 60L, 91L, 91L, 111L, 64L, 114L, 67L, 77L, 132L, 155L, 150L, 164L, 34L, 47L, 47L, 164L, 164L, 66L, 94L, 94L, 106L, 109L, 124L, 131L, 131L, 149L, 151L, 152L, 140L, 141L, 142L, 144L, 143L, 145L, 148L, 101L, 79L, 97L, 129L, 52L, 108L, 162L, 57L, 86L, 163L, 88L, 56L, 83L, 157L, 126L, 156L, 159L, 166L, 172L, 13L, 25L, 35L, 28L, 30L, 33L, 38L, 75L, 69L, 122L, 116L, 117L, 120L, 127L, 128L, 137L, 147L, 152L, 78L, 50L, 139L, 23L, 165L, 41L, 71L, 119L, 141L, 115L, 1L, 48L, 54L, 21L, 99L, 154L, 174L, 63L, 22L, 46L, 107L, 112L, 68L, 160L, 175L, 169L, 168L, 171L, 17L, 32L, 45L, 41L, 44L, 5L, 3L, 12L, 70L, 72L, 128L, 136L, 133L), .Label = c("", "1/11/2019", "1/16/2020", "1/17/2019", "1/2/2020", "1/21/2019", "1/24/2019", "1/25/2019", "1/27/2019", "1/3/201912:00:00AM", "1/30/2019", "1/30/2020", "10/12/2019", "10/17/2018", "10/18/2018", "10/20/2018", "10/21/2019", "10/22/2018", "10/25/2018", "10/3/2018", "10/3/2019", "10/6/2019", "10/7/2019", "10/8/2018", "11/1/2019", "11/17/2018", "11/19/2018", "11/19/2019", "11/21/2018", "11/21/2019", "11/26/2018", "11/28/2019", "11/30/2019", "11/6/201912:00:00AM", "11/9/2019", "12/1/2018", "12/16/2018", "12/18/2019", "12/2/201912:00:00AM", "12/22/2018", "12/25/2019", "12/3/2018", "12/3/201912:00:00AM", "12/30/2019", "12/5/2020", "12/6/2019", "12/6/201912:00:00AM", "13/02/19", "13/02/201900:00", "13/03/19", "13/03/201900:00", "13/04/19", "14/01/201900:00", "14/02/19", "14/03/201900:00", "14/07/19", "15/05/19", "16/02/201900:00", "16/03/201900:00", "16/05/201900:00", "17/01/201900:00", "18/03/201900:00", "18/05/19", "18/05/201900:00", "19/02/201900:00", "19/03/202000:00", "19/05/201900:00", "19/07/19", "2/14/2020", "2/19/2020", "2/2/2020", "2/21/2020", "2/3/2019", "2/3/201912:00:00AM", "2/5/2020", "2/6/2019", "2/6/201912:00:00AM", "20/01/19", "20/02/19", "20/02/201900:00", "20/03/201900:00", "20/05/201900:00", "20/07/19", "21/01/201900:00", "21/02/201900:00", "21/05/19", "22/03/201900:00", "22/06/19", "23/03/201900:00", "23/04/201900:00", "23/05/201900:00", "24/01/201900:00", "24/02/201900:00", "24/03/202000:00", "24/04/201900:00", "25/01/201900:00", "25/02/19", "25/03/201900:00", "25/04/19", "25/04/201900:00", "26/01/19", "26/02/201900:00", "26/03/201900:00", "27/01/201900:00", "27/03/201900:00", "27/03/202000:00", "27/06/19", "28/03/19", "28/03/202000:00", "28/04/201900:00", "28/05/201900:00", "28/06/19", "29/03/201900:00", "29/05/201900:00", "3/1/2019", "3/13/2020", "3/19/2020", "3/2/201912:00:00AM", "3/21/2020", "3/28/2020", "3/3/201912:00:00AM", "3/9/2020", "30/01/201900:00", "30/03/202000:00", "31/01/201900:00", "31/07/19", "4/13/2020", "4/26/2020", "4/3/2019", "4/3/201912:00:00AM", "4/4/202012:00:00AM", "4/6/201912:00:00AM", "5/15/2020", "5/2/201912:00:00AM", "5/2/20196:00:00AM", "5/2/2020", "5/21/2020", "5/3/201912:00:00AM", "5/5/2019", "6/11/2020", "6/14/2020", "6/15/2020", "6/17/2020", "6/18/2020", "6/19/2020", "6/2/201912:00:00AM", "6/2/2020", "6/20/2020", "6/4/2020", "6/6/201912:00:00AM", "6/6/2020", "6/9/2020", "7/3/201912:00:00AM", "7/5/2019", "7/6/201912:00:00AM", "8/14/2019", "8/2/2019", "8/2/201912:00:00AM", "8/25/2019", "8/26/2019", "8/3/201912:00:00AM", "8/5/2019", "8/6/2019", "8/6/201912:00:00AM", "9/12/2019", "9/14/2019", "9/22/2018", "9/22/2019", "9/23/2019", "9/24/2018", "9/28/2019", "9/29/2019", "9/30/2018", "9/5/2019", "9/9/2019"), class = "factor"), Date.3 = structure(c(21L, 31L, 14L, 15L, 16L, 16L, 17L, 18L, 19L, 24L, 24L, 25L, 26L, 26L, 27L, 33L, 35L, 36L, 38L, 38L, 45L, 46L, 55L, 56L, 56L, 47L, 48L, 2L, 5L, 6L, 7L, 9L, 75L, 81L, 84L, 86L, 87L, 87L, 90L, 93L, 93L, 97L, 97L, 114L, 126L, 4L, 4L, 4L, 139L, 139L, 150L, 163L, 163L, 174L, 22L, 34L, 57L, 66L, 66L, 94L, 98L, 101L, 107L, 111L, 111L, 8L, 134L, 142L, 142L, 161L, 161L, 42L, 42L, 52L, 58L, 62L, 64L, 69L, 69L, 88L, 91L, 95L, 95L, 102L, 108L, 108L, 115L, 127L, 127L, 127L, 10L, 10L, 127L, 127L, 127L, 10L, 10L, 129L, 12L, 124L, 136L, 136L, 144L, 96L, 99L, 106L, 106L, 117L, 118L, 13L, 13L, 85L, 125L, 162L, 169L, 30L, 65L, 67L, 74L, 74L, 74L, 92L, 100L, 72L, 103L, 103L, 112L, 116L, 128L, 135L, 135L, 156L, 145L, 146L, 146L, 149L, 149L, 151L, 152L, 153L, 154L, 130L, 105L, 133L, 178L, 70L, 82L, 60L, 89L, 110L, 61L, 113L, 158L, 160L, 166L, 170L, 164L, 167L, 175L, 28L, 20L, 43L, 32L, 37L, 39L, 53L, 49L, 77L, 78L, 120L, 121L, 123L, 137L, 131L, 140L, 141L, 156L, 147L, 104L, 68L, 168L, 157L, 173L, 51L, 83L, 122L, 148L, 171L, 1L, 59L, 63L, 41L, 109L, 168L, 29L, 73L, 44L, 71L, 119L, 155L, 159L, 165L, 172L, 176L, 177L, 179L, 23L, 40L, 54L, 50L, 11L, 11L, 3L, 76L, 79L, 80L, 132L, 143L, 138L), .Label = c("", "1/17/2019", "1/17/2020", "1/2/201912:00:00AM", "1/21/2019", "1/23/2019", "1/28/2019", "1/3/201912:00:00AM", "1/30/2019", "1/4/201912:00:00AM", "1/4/2020", "1/5/201912:00:00AM", "1/6/201912:00:00AM", "10/10/2018", "10/11/2018", "10/12/2018", "10/14/2018", "10/16/2018", "10/18/2018", "10/19/2019", "10/2/2018", "10/2/201912:00:00AM", "10/22/2019", "10/23/2018", "10/27/2018", "10/28/2018", "10/29/2018", "10/3/2019", "10/5/2019", "10/6/201912:00:00AM", "10/7/2018", "11/14/2019", "11/19/2018", "11/2/201912:00:00AM", "11/22/2018", "11/23/2018", "11/24/2019", "11/26/2018", "11/28/2019", "11/29/2019", "11/3/2019", "11/3/201912:00:00AM", "11/5/2019", "11/6/2019", "12/1/2018", "12/2/2018", "12/23/2018", "12/24/2018", "12/25/2019", "12/27/2019", "12/28/2019", "12/3/201912:00:00AM", "12/5/2019", "12/6/2019", "12/7/2018", "12/8/2018", "13/02/201900:00", "13/03/201900:00", "14/02/19", "14/05/19", "14/06/19", "15/03/201900:00", "16/02/19", "16/03/201900:00", "16/06/201900:00", "17/02/201900:00", "17/06/201900:00", "18/03/19", "18/03/201900:00", "18/04/19", "18/06/19", "19/03/202000:00", "19/05/19", "19/06/201900:00", "2/1/2019", "2/1/2020", "2/11/2020", "2/19/2020", "2/21/2020", "2/22/2020", "2/3/2019", "2/5/2019", "2/5/2020", "2/6/2019", "2/6/201912:00:00AM", "2/9/2019", "20/01/201900:00", "20/03/201900:00", "20/05/19", "21/01/201900:00", "21/03/201900:00", "21/06/201900:00", "22/01/201900:00", "22/02/201900:00", "22/03/201900:00", "22/05/201900:00", "23/01/201900:00", "23/02/201900:00", "23/05/201900:00", "23/06/201900:00", "24/02/201900:00", "24/03/201900:00", "24/03/202000:00", "25/01/19", "25/02/19", "25/05/201900:00", "26/02/201900:00", "26/03/201900:00", "26/04/19", "26/05/19", "27/02/201900:00", "27/03/202000:00", "27/06/19", "28/01/201900:00", "28/03/201900:00", "28/03/202000:00", "28/05/201900:00", "29/05/201900:00", "29/06/19", "3/12/2020", "3/18/2020", "3/24/2020", "3/27/2020", "3/5/201912:00:00AM", "3/6/201912:00:00AM", "30/01/201900:00", "30/03/201900:00", "30/03/202000:00", "30/04/201900:00", "31/01/19", "4/20/2020", "4/29/2020", "4/3/2019", "4/3/201912:00:00AM", "4/4/202012:00:00AM", "4/5/201912:00:00AM", "4/5/2020", "5/17/2020", "5/2/201912:00:00AM", "5/2/2020", "5/27/2020", "5/3/201912:00:00AM", "5/4/2020", "5/5/201912:00:00AM", "6/11/2020", "6/14/2020", "6/15/2020", "6/17/2020", "6/18/2020", "6/2/201912:00:00AM", "6/20/2020", "6/21/2020", "6/22/2020", "6/23/2020", "6/29/2019", "6/9/2020", "7/15/2019", "7/18/2019", "7/22/2019", "7/27/2019", "7/3/201912:00:00AM", "7/6/201912:00:00AM", "8/2/201912:00:00AM", "8/20/2019", "8/28/2019", "8/3/2019", "8/31/2019", "8/5/2019", "8/6/201912:00:00AM", "8/8/2019", "9/1/2019", "9/10/2019", "9/16/2019", "9/2/201912:00:00AM", "9/20/2019", "9/28/2019", "9/29/2019", "9/3/2019", "9/30/2019"), class = "factor")), class = "data.frame", row.names = c(NA, -219L))

Expected output Date.1 Date.2 Date.3 9/21/2018 9/22/2018 10/2/2018 9/24/2018 9/24/2018 10/7/2018 10/3/2018 10/3/2018 10/10/2018 9/24/2018 9/24/2018 10/11/2018 9/22/2018 9/22/2018 10/12/2018 10/8/2018 10/8/2018 10/12/2018 9/30/2018 9/30/2018 10/14/2018 ... For all rows

xyz
  • 79
  • 1
  • 8

2 Answers2

0

Maybe you can try as.Date + format like below

s <- c("2/3/2019","2/6/2020","1/2/2019 0:00")
format(as.Date(s,format = "%d/%m/%Y"),"%m/%d/%Y")
# [1] "03/02/2019" "06/02/2020" "02/01/2019"
ThomasIsCoding
  • 96,636
  • 9
  • 24
  • 81
0

You can use parse_date_time to parse any date format and format to reformat them

Data:

x <- c("2/3/2019","02-6-2020","1/2/2019 0:00")

Solution:

library(lubridate)
formats <- c("%d/%m/Y", "%d-%m-%Y", "%d/%m/Y %H:%M")
format(parse_date_time(x, orders = formats), "%m-%d-%Y")
Chris Ruehlemann
  • 20,321
  • 4
  • 12
  • 34