Menu
I have a vector of numeric excel dates i.e.
date <- c(42963,42994,42903,42933,42964)
The output am I expecting when using excel_numeric_to_date function from janitor package and as.yearmon function from zoo package
as.yearmon(excel_numeric_to_date(date)) [1] "Aug 2016" "Sep 2016" "Jun 2017" "Jul 2017" "Aug 2017".
However, the conversion for the first to elements of the date vector are incorrect. The actual result are:
as.yearmon(excel_numeric_to_date(date)) [1] "Aug 2017" "Sep 2017" "Jun 2017" "Jul 2017" "Aug 2017"
I have tried using different option(modern and mac pre-2011) for the date_system argument in the excel_numeric_to_date but it does not help either
The excel version is 2010
203
On the Home tab, in the Number group, click the Dialog Box Launcher next to Number. You can also press CTRL+1 to open the Format Cells dialog box. In the Category list, click Date or Time. In the Type list, click the date or time format that you want to use.
Convert serial number to date with formula 1. Select a blank cell (says cell B2) adjacent to the serial number cell you need to convert to date, then enter formula =TEXT(A2,"m/d/yyyy") into the Formula Bar, and press the Enter key.
The keyboard shortcut to insert the current date in Excel is: Ctrl + ; (semicolon). After pressing the shortcut, the date will be input and the active cell will be in edit mode. Press Enter to confirm the change.
On the Data tab of the ribbon, click Text to Columns. Click Next >, then Next > again. Under 'Column data format', select Date, then select YMD from the drop-down next to the Date option button. Click Finish.
If you want to convert dates from Excel, you can use as.Date() with a specific origin. According to the documentation, "1900-01-0"' is used as day in Excel on Windows, but "this is complicated by Excel incorrectly treating 1900 as a leap year". So "1899-12-30" should be used for dates post 1901:
date <- c(42963,42994,42903,42933,42964)
This is the result of as.Date():
as.Date(date, origin = "1899-12-30")
[1] "2017-08-18" "2017-09-18" "2017-06-19" "2017-07-19" "2017-08-19"
You can then use zoo::as.yearmon()` to get the expected outcome:
zoo::as.yearmon(as.Date(date, origin = "1899-12-30"))
[1] "Aug 2017" "Sep 2017" "Jun 2017" "Jul 2017" "Aug 2017"
You can simply use as.Date and specify the origin, i.e.
as.Date(date, origin="1899-12-30")
#[1] "2017-08-16" "2017-09-16" "2017-06-17" "2017-07-17" "2017-08-17"
#or format it to your liking,
format(as.Date(date, origin="1899-12-30"), '%b %Y')
#[1] "Aug 2017" "Sep 2017" "Jun 2017" "Jul 2017" "Aug 2017"
This link gives quite a bit of information on this matter.
101
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
