Date/Time formatting with variables containing day of the week

June 7, 2022

I’m trying to format a date column that contains the day of the week. When I try to format it using my usual method my time is always 00:00. Here is an example:

a <- c(1:6)
b <- c("Tuesday, 05 December 2012 05:00","Tuesday, 05 December 2012 06:55","Tuesday, 05 December 2012 07:10",
       "Tuesday, 05 December 2012 10:23", "Tuesday, 05 December 2012 11:43","Tuesday, 05 December 2012 13:04")
c <-c("0","0","0","1","1","1")
df1 <- data.frame(a,b,c,stringsAsFactors = FALSE)

If I try to change the format using this code:

df1 <- df1 %>%
  mutate(DateTime = format(as.Date(b, format = "%A, %d %B %Y %H:%M"), "%d-%m-%Y %H:%M"))

I get a response where the hours and minutes are always 00:00. I believe this has something to with the date not being POSIXct/POSIXlt format, but I’m not sure.

  a                            Date c         DateTime
1 1 Tuesday, 05 December 2012 05:00 0 05-12-2012 00:00
2 2 Tuesday, 05 December 2012 06:55 0 05-12-2012 00:00
3 3 Tuesday, 05 December 2012 07:10 0 05-12-2012 00:00
4 4 Tuesday, 05 December 2012 10:23 1 05-12-2012 00:00
5 5 Tuesday, 05 December 2012 11:43 1 05-12-2012 00:00
6 6 Tuesday, 05 December 2012 13:04 1 05-12-2012 00:00

Thanks!

>Solution :

Here another approach:

# data 
b <- c("Tuesday, 05 December 2012 05:00","Tuesday, 05 December 2012 06:55","Tuesday, 05 December 2012 07:10",
       "Tuesday, 05 December 2012 10:23", "Tuesday, 05 December 2012 11:43","Tuesday, 05 December 2012 13:04")

# remove everything before first digit
b <- sub("^\\D+", "", b)

# use ydm_hm() from {lubridate}
library(lubridate)
ydm_hm(b)
#> [1] "2005-12-20 05:00:00 UTC" "2005-12-20 06:55:00 UTC"
#> [3] "2005-12-20 07:10:00 UTC" "2005-12-20 10:23:00 UTC"
#> [5] "2005-12-20 11:43:00 UTC" "2005-12-20 13:04:00 UTC"

^{Created on 2022-06-07 by the reprex package (v2.0.1)}