0

I am trying to read an excel file containing some date variable. But when I use the function to read the file and display the variable I get different format for two of my variables as in these following example.

date1
[1] "2009-05-22 UTC" "1995-09-13 UTC" "1996-03-20 UTC" "2006-17-05 UTC" "2005-09-25 UTC" "2004-12-15 UTC" NA               NA               "1997-05-30 UTC"


date2
[1] "2016-05-07 08:57:00 UTC" "2014-07-22 19:01:00 UTC" "2018-12-02 12:14:00 UTC" "2020-01-11 13:27:00 UTC" "2013-14-21 11:40:00 UTC"
[6] "2019-08-15 09:31:00 UTC" NA                        NA                        "2016-07-23 00:00:00 UTC"

How can I convert format of date2 in format of date1

Aurelia Kurtis
  • 73
  • 1
  • 6

1 Answers1

2

You could try to coerce as.Date which will remove time information, like so:

as.numeric(as.POSIXct(as.Date(x2))) |> 
  as.POSIXct(origin=as.POSIXct('1970-01-01', tz='UTC'), tz='UTC')
# [1] "2016-05-07 UTC" "2014-07-22 UTC" "2018-12-02 UTC" "2020-01-11 UTC"
# [5] NA               "2019-08-15 UTC" NA               NA              
# [9] "2016-07-23 UTC"

Definitely see also this great answer.

Note:

R.version.string
# [1] "R version 4.1.2 (2021-11-01)"

Data:

x1 <- structure(c(1242950400, 810950400, 827280000, NA, 1127606400, 
1103068800, NA, NA, 864950400), class = c("POSIXct", "POSIXt"
), tzone = "UTC")

x2 <- structure(c(1462611420, 1406055660, 1543752840, 1578749220, NA, 
1565861460, NA, NA, 1469232000), class = c("POSIXct", "POSIXt"
), tzone = "UTC")
jay.sf
  • 60,139
  • 8
  • 53
  • 110