Change POSIXct format from YYYY-MM-DD to MM-DD-YYYY in R

Question

New R user here. I import an xlxs using read_xlsx function. Two columns I read as date, the rest as text or numeric. After, I am unable to convert column "EDD" from YYYY-MM-DD to MM-DD-YYYY. Str shows POSIXct.

MICAP <- read_xlsx("~/Copy of Joint MICAP Report 0700 CDT (004).xlsx", 
sheet = "OPEN ORDERS for MICAP Reporting", col_names = TRUE, , 
col_types = c("text", "text", "text", "text", "text", "text", 
"text", "text", "text", "text", "date", "numeric", "text", 
"date", "text", "text", "text", "text", "text"))

When I call str(MICAP), my column, EDD (date) shows as POSIXct but I'm unable to change.

I'm concerned only with EDD's change to MM/DD/YYYY

`POSIXt`-class variables are shown one way in R, and that's with `YYYY-MM-DD` (and usually but not always `HH:MM:SS` with/without decimal seconds based on `options("digits.secs")`). If you want it displayed differently, you can either (1) convert to string using `format(vec, format="%m-%s-%Y")`; or (2) super-class it, and use R's S3 method dispatch to change how it is *displayed* on the console, and hope that you find all methods needed to get all of your needs. — r2evans, Oct 22 '21 at 12:53
@JLGRIECO, another thought: changing it to a string can be problematic if you ever want to do number-like things to it (`diff`, `range`, `order`/`arrange`, etc), and too often the rationale for doing this is about how it is *rendered* in a final report/table. If that's the case, then `format` is your answer, informed by the `%`-strings found in `?strptime`. If you truly want this just for display on your console, though, I suggest either (1) accept R's default and work through this difference, or (2) you will need to become comfortable enough with S3 to understand what I mean by "super-class". — r2evans, Oct 22 '21 at 13:22

score 0 · Answer 1 · answered Oct 22 '21 at 13:40

This is what I mean by super-classing it:

format.myPOSIX <- function(x, tz = "", usetz = FALSE, ...) {
  dots <- list(...)
  fmt <- NA
  if ("format" %in% names(dots)) {
    fmt <- dots$format
    dots$format <- NULL
  }
  if (inherits(x, "Date")) {
    if (is.na(fmt)) fmt <- "%m/%d/%Y"
    func <- match.fun("format.Date")
  } else { # POSIXt
    if (is.na(fmt)) fmt <- "%m/%d/%Y %H:%M:%S"
    func <- match.fun("format.POSIXct")
  }
  do.call(func, c(list(x, tz = tz, usetz = usetz), format = fmt, dots))
}
print.myPOSIX <- function(x, tz = "", usetz = FALSE, ...) {
  print(format.myPOSIX(x, tz = tz, usetz = usetz))
}

Example, since your data is not available.

set.seed(42)

datevec <- sort(Sys.Date() + sample(20, size=3))
class(datevec) <- c("myPOSIX", class(datevec))
datevec
# [1] "10/23/2021" "10/27/2021" "11/08/2021"
diff(datevec)
# Time differences in days
# [1]  4 12

psxvec <- sort(Sys.time() + sample(2000, size=3))
class(psxvec) <- c("myPOSIX", class(psxvec))
psxvec
# [1] "10/22/2021 09:55:28" "10/22/2021 09:56:47" "10/22/2021 09:58:02"
diff(psxvec)
# Time differences in mins
# [1] 1.316667 1.250000

showing that the printed format is "%m/%d/%Y" yet numeric operations still function.

This should also work in data.frame and similar structures:

data.frame(dt = datevec, psx = psxvec)
#           dt                 psx
# 1 10/23/2021 10/22/2021 09:58:02
# 2 10/27/2021 10/22/2021 09:59:21
# 3 11/08/2021 10/22/2021 10:00:36
tibble::tibble(dt = datevec, psx = psxvec)
# # A tibble: 3 x 2
#   dt         psx                
#   <date>     <dttm>             
# 1 2021-10-23 2021-10-22 09:58:02
# 2 2021-10-27 2021-10-22 09:59:21
# 3 2021-11-08 2021-10-22 10:00:36
data.table::data.table(dt = datevec, psx = psxvec)
#            dt                 psx
#     <myPOSIX>           <myPOSIX>
# 1: 10/23/2021 10/22/2021 09:58:02
# 2: 10/27/2021 10/22/2021 09:59:21
# 3: 11/08/2021 10/22/2021 10:00:36

Change POSIXct format from YYYY-MM-DD to MM-DD-YYYY in R

1 Answers1