Default date formatting

[lz1nwm]

Why the default date format does not match Excel default date format (Windows Regional setting)? The following code formats date as mm.dd.yyyy, while my Excel default date formatting is *dd.mm.yy. There was no such issue in version 4.2.3.

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, sheetName = 'Sheet')
writeData(wb, sheet = 'Sheet', x = seq.Date(as.Date('2022-01-01'),as.Date('2022-01-14'), by=1))
openXL(wb)

Session info

#> - Session info ---------------------------------------------------------------
#>  setting  value
#>  version  R version 4.1.3 (2022-03-10)
#>  os       Windows 7 x64 (build 7601) SP 1
#>  system   x86_64, mingw32
#>  ui       RTerm
#>  language (EN)
#>  collate  English_United States.1252
#>  ctype    English_United States.1252
#>  tz       Europe/Helsinki
#>  date     2022-06-30
#> 
#> - Packages -------------------------------------------------------------------
#>  package     * version    date (UTC) lib source
#>  openxlsx    * 4.2.5.9000 2022-06-29 [1] Github (ycphs/openxlsx@3ceeb84)
#> 
#> ------------------------------------------------------------------------------

[JanMarvin]

Hi @lz1nwm , we fixed a bug when writing dates, which might have triggered the behavior you have encountered.

If you want to fix that, you could try to apply a special date format. If you want to fix this on the code level, try to find out, what has changed since the last working version. git bisect could help, but there haven't been so many changes on the development branch, you could simply try a few of them.

[deschen1]

I checked it in the development branch (https://github.com/ycphs/openxlsx/tree/development). Not entirely sure what the expected behaviour is.

Running the code above returns a "custom" column format as mm.dd.yyyy, however, I can easily switch that in Excel to "Date", which then returns the date format as specified by the WInDows Regional settings, in my case dd.mm.yyyy

[JanMarvin]

There is indeed a change that has been around sind 4.2.4 and might be an unrelated side effect. My first guess is that it is related to this PR I assume: #165 from April 2021. Wild unelaborated guess maybe when replacing this:

-numFmt <- getOption("openxlsx.dateFormat", getOption("openxlsx.dateformat", "date"))
+numFmt <- openxlsx_getOp("dateFormat", "date")

It can be changed the output to your expected setting with the following:

# the previous default: 14.01.22
options("openxlsx.dateFormat" = "date")
# or a longer custom date string: 14.01.2022
options("openxlsx.dateFormat" = "dd/mm/yyyy")

Anyone care to dig in to the code where we switched the default? I have done my deed here, but it might be fruitful to change the default back again. Otherwise us people in old Europe might get a few headaches. The value is shown as 14.01.2022, but the format is 01.14.2022.

[JanMarvin]

Pushed a fix to development. Please let me know if this solves the issue you were seeing.

[lz1nwm]

@JanMarvin thanks for the efforts, but I see no change after the fix. It is still formatted as mm.dd.yyyy.

But if I set options("openxlsx.dateFormat" = "date") I get expected output and it solves my issue. So I'll live with it if most of the users prefer this behavior.

[JanMarvin]

The intention of the fix is to set just this options("openxlsx.dateFormat" = "date"). Did you rebuild and install development in a new session?

[lz1nwm]

I'm sorry, you are right. I have rebuild it in a clean new session and it works now. Although I have this warnings in the end:

Warning messages:
1: In untar2(tarfile, files, list, exdir) :
  skipping pax global extended headers
2: In untar2(tarfile, files, list, exdir) :
  skipping pax global extended headers

Thanks again @JanMarvin !

[lz1nwm]

So, the issue is still here. When I confirmed that it was solved, I have run the code with the option. Without this option in the code there is no change...

[JanMarvin]

And your sure that you installed the development branch? I'm asking because I'm not sure, where else we pick up some other date, but I might have missed it in the code. Maybe someone else will pick this up and continue this. If the issue remains. I've already spent more than enough time researching the issue yesterday evening.

[lz1nwm]

I'm using openxlsx * 4.2.5.9000 2022-07-07 [1] Github (ycphs/openxlsx@3ceeb84)

[lz1nwm]

My fault. I thought that the main branch here is the development one. Now I realized that I have to use remotes::install_github("ycphs/openxlsx", ref = "development") to have the development version. That said, after installing openxlsx * 4.2.5.1 2022-07-07 [1] Github (ycphs/openxlsx@875d38e) the issue seems resolved and it will be great if the fix could be merged to master branch.

[JanMarvin]

Thanks for confirming. Yes, the fix is in the development branch an will be merged once we draft the next release from there.

[github-actions]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions]

This issue was closed because it has been stalled for 7 days with no activity.