I have a spreadsheet that has the date and 12 hour time in one column and then another column that specifies AM/PM. How do I combine these files so I can use them as a POSIXct/POSIXlt/POSIXt object?

The spreadsheet has the time column as DAY/MONTH/YEAR HOUR:MINUTE

while hour is in a 12-hour format from a roster of check in times. The other column just says AM or PM. I am trying to combine these columns and then convert them to 24 hour time and use it as a POSIXt object.

Example of what I see:

And this continues until 5:00 PM (same day)

What I have tried so far:

Timesheet %>%
  unite("timestamp_24", c("timestamp_12","am_pm"),na.rm=FALSE)%>%
  mutate(timestamp=(as.POSIXct(timestamp, format = "%d-%m-%Y %H:%M"))

This does not work as when they are combined it gives: Timestamp_24 DAY/MONTH/YEAR HOUR:MINUTE_AM and I think this is the crux of the issue because then as.POSIXct can't read it.

CodePudding user response：

Here's my solution. The approach is simply to extract the hour, 12 if it is PM, then format correctly with as.POSXct (you need to use / rather than - in the format argument if the your dataframe is at is appears in your example). I've done that with stringr::str_replace() which allows you to set a function for the replace argument.

Timesheet <- data.frame(
  time = c("8/10/2022 9:00", "8/10/2022 9:01"),
  am_pm = c("AM", "PM")
)

Timesheet %>% 
  mutate(
    time_24hr = stringr::str_replace(
        time, 
        ".(?=:..$)",
        function(x) ifelse(am_pm == "PM", as.numeric(x)   12, x)
    ),
    time_24hr = as.POSIXct(time_24hr, format = "%d/%m/%Y %H:%M")
  )

This is the result:

            time am_pm           time_24hr
1 8/10/2022 9:00    AM 2022-10-08 09:00:00
2 8/10/2022 9:01    PM 2022-10-08 21:01:00