R - Convert Float number to Date

user_3826025 0
Viewed 696

Wondering if you can give me some light about an outstanding problem I have in R, version 4.0.2. I am moving data from an excel file in a given sharepoint to a local DB. The problem facing is that when reading the file using readxl the columns in the original file as dates (datetime) are being returned as floating point numbers. I need to change the number back to datetime.

Libraries used:

library(readxl)
library(odbc)
library(lubridate)

Number

44047.8149884259

44055.2403009259

44048.504537037

Expected result

8/4/2020  7:33:35 PM

8/12/2020  5:46:02 AM

8/5/2020  12:06:32 PM

I've tried to use as_date with different formats and as.POSIXct but don't seem to have an answer.

Thanks in advance for your kindly inputs.

r datetime
1 Answers

We could use

format(as.POSIXct(v1 * 60 *60 * 24, origin = '1899-12-30', tz = 'UTC'),
        '%m/%d/%Y %I:%M:%S %p')
#[1] "08/04/2020 07:33:34 pm" "08/12/2020 05:46:01 am" "08/05/2020 12:06:31 pm"

data

v1 <- c(44047.8149884259, 44055.2403009259, 44048.504537037)
user_37322710
Related