R: converting original excel dates to the format yyyy-mm-dd HH:MM:SS-CodePudding

I've read an excel files with dates in different formats into R. Some are correctly read in the format "yyyy-mm-dd HH:MM:SS" and those who have had another format in excel before are now numbers like: 44586.727083333302 (stands for 25.01.2022 17:27:00)

I tried to convert them:

as.Date(df$dates, format='%Y-%m-%d %H:%M:%S', origin = "1900-01-01 24:00:00")

But R gives me just yyyy-mm-dd and HH:MM:SS is missing.

I need the timestamp as well. Does anyone know how the code must be?

CodePudding user response：

You'll have to use the as.POSIXct() function instead of the as.Date() function. as.Date() returns the day without the time. The formatting you did should be the same.

CodePudding user response：

We could use the convertDateTime function:

library(openxlsx)

string <- 44586.727083333302
convertToDateTime(string, origin = "1900-01-01")

#or for your data frame:
convertToDateTime(df$dates, origin = "1900-01-01")

[1] "2022-01-25 17:27:00 CET"