Dates in various formats

Hi, I am having troubles getting the right format regarding dates in my data frame.

Some dates are listed as excel number dates (example: "44470") and some are listed as a string (example: 01-Jul-2021).

I have tried to format the entire column to numeric dates but this only change the excel number dates and puts NA in the rest.

Any Ideas would be appreciated

image

Hi,

Dates in excel are always giving trouble :(. There are a few options I always try:
1: go back to source if you can and fix it there.
2: if you import it from excel as '* .csv' try reading from excel directly e.g. using readxl() or openxls() . Or, case you already read directly from the excel format, try other package or import as .csv by exporting from excel first (check char encoding is UTF-8)
3: if there are only a few different entries, just use dplyr::mutate(is.numeric()) to change them individually within R. Best to use lubridate when dealing with different formats.

Please note that for number conversion, the reference date is different when comparing excel and R. I think excel starts at 1900, so you need to use 'origin', e. g. as.Date(44405, origin = "1899-12-30") for today.

Hope it helps.

JW

1 Like

Thank you so much for your input! I will try it out and I will let you know how it goes.

All the best
/Anders

In case you can't clean the source file, you could do something like this

library(tidyverse)
library(lubridate)

df <- data.frame(
    start_date = c("01-Jul-2021", "44228", "01-Jul-2021", "44440")
)

df %>%
    mutate(
        start_date = if_else(str_detect(start_date, "\\d{1,2}-"),
                             dmy(start_date),
                             as.Date(as.numeric(start_date), origin = "1899-12-30"))
    )

#>   start_date
#> 1 2021-07-01
#> 2 2021-02-01
#> 3 2021-07-01
#> 4 2021-09-01

Created on 2021-07-30 by the reprex package (v2.0.0)

1 Like

Thanks a lot for your reply and suggested solution! I will try it out next time I am working on my project and post if it solved my problem (Y)

All the best
/A

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.