Error when mutating dates

R code
,
1

Hello, I need help with a message error I get when I try to convert a date into dmy format; the error says ‘No formats found’
I am sharing my full code below

pacman:: p_load(rio, janitor, tidyverse, here, datapasta, reprex)

mydferror <-  data.frame(
  stringsAsFactors = FALSE,
  date_min = c("44393",
               "44407","44432","44369","44382","44207",
               "43781","44384","44406","44397"),
  Age = c("28",
          "34","51","43","64","42","44","51","62",
          "34"))

mydf_clean <- mydferror %>% 
  mutate(date_min = dmy(date_min))
#> Warning: There was 1 warning in `mutate()`.
#> ℹ In argument: `date_min = dmy(date_min)`.
#> Caused by warning:
#> ! All formats failed to parse. No formats found.

head(mydf_clean$date_min)
#> [1] NA NA NA NA NA NA

Created on 2024-03-04 with reprex v2.0.2

Session info 
sessionInfo()
#> R version 4.2.2 (2022-10-31 ucrt)
#> Platform: x86_64-w64-mingw32/x64 (64-bit)
#> Running under: Windows 10 x64 (build 19045)
#> 
#> Matrix products: default
#> 
#> locale:
#> [1] LC_COLLATE=English_United Kingdom.utf8 
#> [2] LC_CTYPE=English_United Kingdom.utf8   
#> [3] LC_MONETARY=English_United Kingdom.utf8
#> [4] LC_NUMERIC=C                           
#> [5] LC_TIME=English_United Kingdom.utf8    
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#>  [1] reprex_2.0.2    datapasta_3.1.0 here_1.0.1      lubridate_1.9.2
#>  [5] forcats_1.0.0   stringr_1.5.0   dplyr_1.1.3     purrr_1.0.1    
#>  [9] readr_2.1.4     tidyr_1.3.0     tibble_3.2.1    ggplot2_3.4.2  
#> [13] tidyverse_2.0.0 janitor_2.2.0   rio_0.5.29     
#> 
#> loaded via a namespace (and not attached):
#>  [1] tidyselect_1.2.0  xfun_0.39         haven_2.5.3       snakecase_0.11.0 
#>  [5] colorspace_2.0-3  vctrs_0.6.1       generics_0.1.3    htmltools_0.5.5  
#>  [9] yaml_2.3.6        utf8_1.2.2        rlang_1.1.0       pillar_1.9.0     
#> [13] foreign_0.8-83    glue_1.6.2        withr_2.5.0       readxl_1.4.3     
#> [17] lifecycle_1.0.3   munsell_0.5.0     gtable_0.3.1      cellranger_1.1.0 
#> [21] zip_2.2.2         evaluate_0.18     knitr_1.41        tzdb_0.3.0       
#> [25] fastmap_1.1.0     curl_4.3.3        fansi_1.0.3       highr_0.9        
#> [29] Rcpp_1.0.9        scales_1.2.1      fs_1.6.3          hms_1.1.2        
#> [33] digest_0.6.30     stringi_1.7.8     openxlsx_4.2.5.1  rprojroot_2.0.3  
#> [37] grid_4.2.2        cli_3.6.1         tools_4.2.2       magrittr_2.0.3   
#> [41] pacman_0.5.1      pkgconfig_2.0.3   ellipsis_0.3.2    data.table_1.14.8
#> [45] timechange_0.2.0  rmarkdown_2.22    rstudioapi_0.14   R6_2.5.1         
#> [49] compiler_4.2.2

Follow-up

  • Thank the volunteers who try to help you
  • Mark one reply as the “Solution” if appropriate
1 Like
2

Hi @selene and welcome to the forum!

It seems that you are using the dmy() function on character values that seem to be from Excel.

  1. I suggest converting those numbers to numeric class
  2. To correctly help R interpret these numbers as Excel dates, use the excel_numeric_to_date() function from janitor package.

Full code below,
Hope this helps!
Neale

pacman:: p_load(rio, janitor, tidyverse, here, datapasta, reprex)

mydferror <-  data.frame(
  stringsAsFactors = FALSE,
  date_min = c("44393",
               "44407","44432","44369","44382","44207",
               "43781","44384","44406","44397"),
  Age = c("28",
          "34","51","43","64","42","44","51","62",
          "34"))

mydf_clean <- mydferror %>% 
  mutate(date_min = as.numeric(date_min)) %>% 
  mutate(date_min = janitor::excel_numeric_to_date(date_min))

head(mydf_clean$date_min)
1 Like