0

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?

lama06
  • 9
  • 1

2 Answers2

0

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.

David Moore
  • 670
  • 3
  • 15
0

Update: request OP:

You should install parsedate package:

enter image description here

library(parsedate)

#your df:
                 date
1 2018-06-30 12:09:34
2  44586.727083333302

# with this code:
dat %>% 
  mutate(x = parse_date(date))

you get this

                 date                   x
1 2018-06-30 12:09:34 2018-06-30 12:09:34
2  44586.727083333302 2022-07-31 15:39:06

First answer: 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"
TarJae
  • 72,363
  • 6
  • 19
  • 66
  • Thank you! This was very helpful. Now I do have the problem though that I want to overwrite just a part of the data, because the other columns have already this format. When I try this: df$dates[5391:6802] <- convertToDateTime(df$dates`[5391:6802], origin = "1900-01-01") There are new numbers like 1643128020.. any idea how to fix this? – lama06 Jul 31 '22 at 12:36
  • ? Do not understand. Could you please clarify. Thanks. – TarJae Jul 31 '22 at 12:38
  • 1
    Sorry I was still editing. Is it clear the way I described it now? – lama06 Jul 31 '22 at 12:56
  • For the numbers like 1643128020 use this: `library(parsedate) parse_date(1643128020)` Or provide a little mor info. Like some rows of the different numbers etc.... – TarJae Jul 31 '22 at 13:10
  • Unfortunately, my RStudio version can't load the parse_date package. But here some more information about my code and data: Right now up to column 5390 I do have this format for dates: 2018-06-30 12:09:34 Then from 5391 I have those numbers for dates 44586.7270 When I run this code: 'convertToDateTime(df$dates[5391:6802], origin = "1900-01-01")' then it gives the correct format: 2021-10-09 18:04:00 CEST But I want to overwrite those numbers like 44586.727083333302 When I try this by using this code: df$dates[5391:6802] <- convertToDateTime(df$dates[5391:6802], origin = "1900-01-01") – lama06 Jul 31 '22 at 13:31
  • Then I receive these numbers in my df : 1640346660 – lama06 Jul 31 '22 at 13:32
  • Please see my update – TarJae Jul 31 '22 at 13:40
  • 1
    Thank you for your update. Unfortunately it doesn't work or I have a mistake in my code. Is it dat = df? df %>% mutate(x = parse_date(dates)) then it goes over every variable and the ones that should be overwritten still are the same.. – lama06 Jul 31 '22 at 14:41