Menu
Excel stores dates internally as floating-point serial date. We know how to convert it into R - using as.Date(var2, origin="1899-12-30", tz='UTC').
The question is: how to convert it back into Excel? I tried all the obvious things, but I still get a mismatch.
444
Convert date to serial number with DATEVALUE Also, you can apply the DATEVALUE function to convert date cells to serial numbers. Select a blank cell which will place the serial number, type this formula =DATEVALUE("8/8/2017"), press Enter key. Now the date in formula will be displayed as serial number.
You can use the as. Date( ) function to convert character data to dates. The format is as. Date(x, "format"), where x is the character data and format gives the appropriate format.
Simply format the Date in that way, then call as. integer on the result. If your date isn't a Date, and is a character string: either convert it to a Date first, or remove the "-" before calling as.
One approach is, for example for the date '2017-05-15' :
d0 <- as.Date(0, origin="1899-12-30", tz='UTC')
d1 <- as.Date('2017-05-15')
d <- as.numeric(d1-d0)
# or in one line
d <- as.numeric(as.Date('2017-05-15') -as.Date(0, origin="1899-12-30", tz='UTC'))
print(d)
[1] 42870
In excel the date '2017-05-15' gives the same number when expressed as numeric serial date.
143
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
