DeaR useR, I read an excel column in R having Date and time (written in the same cell) as follow, 06/18/18 10:00 06/18/18 11:00 06/18/18 12:00 In R environment, they are read as 43269.42 43269.46 43269.50 Is there a way to covert these characters back to the original format? Thank-you very much in advance. Eliza Botto [[alternative HTML version deleted]]
Hello,
I'm assuming you're reading from an "*.xlsx" file. I'm not
sure which
package you're using for this scenario, but my preference is
'openxlsx'. If
this is the package you're using, you could set argument
'detectDates' to
'TRUE', and then it should read them correctly.
FILE <- tempfile(fileext = ".xlsx")
openxlsx::write.xlsx(
data.frame(V1 = as.Date("2000-01-01")),
FILE
)
openxlsx::read.xlsx(FILE)
openxlsx::read.xlsx(FILE, detectDates = TRUE)
unlink(FILE)
The first one should read the dates as numbers (days since 1900-01-01 or
1904-01-01, depending upon setup), while the second should parse them to
class "Date". I hope this helps!
On Tue, Aug 31, 2021 at 4:26 PM Eliza Botto <eliza_botto at outlook.com>
wrote:
> DeaR useR,
>
> I read an excel column in R having Date and time (written in the same
> cell) as follow,
>
> 06/18/18 10:00
>
> 06/18/18 11:00
>
> 06/18/18 12:00
>
> In R environment, they are read as
>
> 43269.42
>
> 43269.46
>
> 43269.50
>
> Is there a way to covert these characters back to the original format?
>
> Thank-you very much in advance.
>
>
> Eliza Botto
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
[[alternative HTML version deleted]]
On Tue, 31 Aug 2021, Eliza Botto writes:> DeaR useR, > > I read an excel column in R having Date and time (written in the same cell) as follow, > > 06/18/18 10:00 > > 06/18/18 11:00 > > 06/18/18 12:00 > > In R environment, they are read as > > 43269.42 > > 43269.46 > > 43269.50 > > Is there a way to covert these characters back to the original format? > > Thank-you very much in advance. > > > Eliza Botto >If using a package is an option: library("datetimeutils") convert_date(c(43269.42, 43269.46, 43269.50), "excel") ## [1] "2018-06-18" "2018-06-18" "2018-06-18" convert_date(c(43269.42, 43269.46, 43269.50), "excel", fraction = TRUE) ## [1] "2018-06-18 10:04:48 CEST" "2018-06-18 11:02:24 CEST" ## [3] "2018-06-18 12:00:00 CEST" Note that the times differ: the numbers are probably not /displayed/ to full precision in R. You may also want to search the archives of this list, as this question has been discussed before. -- Enrico Schumann (maintainer of package datetimeutils) Lucerne, Switzerland http://enricoschumann.net
Hi You can use as.POSIXct function https://stackoverflow.com/questions/19172632/converting-excel-datetime-seria l-number-to-r-datetime But you should preferably try to read the date as character vector and then convert it to date and time. Cheers Petr> -----Original Message----- > From: R-help <r-help-bounces at r-project.org> On Behalf Of Eliza Botto > Sent: Tuesday, August 31, 2021 10:26 PM > To: r-help at r-project.org > Subject: [R] Converting characters back to Date and Time > > DeaR useR, > > I read an excel column in R having Date and time (written in the samecell) as> follow, > > 06/18/18 10:00 > > 06/18/18 11:00 > > 06/18/18 12:00 > > In R environment, they are read as > > 43269.42 > > 43269.46 > > 43269.50 > > Is there a way to covert these characters back to the original format? > > Thank-you very much in advance. > > > Eliza Botto > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting- > guide.html > and provide commented, minimal, self-contained, reproducible code.