I've usually had good luck with this, but something is not working well. I have two datetimes in excel 7/20/21 13:30 7/20/21 13:40 And when I convert these to excel's normal storage schema, I get the following: 42935.5625 42935.56944 Just try to convert this to a POSIX class gives me issues.> dt <- c(42935.5625,42935.5694444444)> as.POSIXct(dt,origin="1899-12-30 00:00:00",tz="GMT")[1] "1899-12-30 11:55:36 GMT" "1899-12-30 11:55:36 GMT" As you can see, there is a world of difference here. I've tried any number of solutions such as lubridate, etc and I get the same result> as_datetime(dt,origin="1899-12-30 00:00:00")[1] "1899-12-30 11:55:36 UTC" "1899-12-30 11:55:36 UTC" Any ideas about what I'm doing wrong? Shawn Way
Hi Shawn,
I don't have any trouble with this:
times<-c("7/20/21 13:30","7/20/21 13:40")
strptime(times,"%m/%d/%y %H:%M",tz="GMT")
[1] "2021-07-20 13:30:00 GMT" "2021-07-20 13:40:00 GMT"
I suspect that Excel is causing the problem. Try changing the format
of the date column to "Text" and work on the character representation
of the dates.
Jim
On Thu, Jul 22, 2021 at 8:49 AM Shawn Way <sway at empowerpharmacy.com>
wrote:>
> I've usually had good luck with this, but something is not working
well. I have two datetimes in excel
>
> 7/20/21 13:30
> 7/20/21 13:40
>
> And when I convert these to excel's normal storage schema, I get the
following:
>
> 42935.5625
> 42935.56944
>
> Just try to convert this to a POSIX class gives me issues.
>
> > dt <- c(42935.5625,42935.5694444444)
>
> > as.POSIXct(dt,origin="1899-12-30
00:00:00",tz="GMT")
>
> [1] "1899-12-30 11:55:36 GMT" "1899-12-30 11:55:36 GMT"
>
> As you can see, there is a world of difference here. I've tried any
number of solutions such as lubridate, etc and I get the same result
>
> > as_datetime(dt,origin="1899-12-30 00:00:00")
>
> [1] "1899-12-30 11:55:36 UTC" "1899-12-30 11:55:36 UTC"
>
> Any ideas about what I'm doing wrong?
>
>
> Shawn Way
>
> ______________________________________________
> 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.
Hello,>From playing around with your numbers, it seems like you are using Excel1904 Date System, which isn't a problem, it just means that your numbers are days from 1904-01-01 instead of 1900-01-01. The following is my solution: times <- c(42935.5625,42935.5694444444) as.POSIXlt(( # offset the days by the origin for Excel times + unclass(as.Date("1904-01-01")) # multiply by 86400 to convert number of days to number of seconds ) * 86400, # .Date(0) is the origin day for R origin = .Date(0), tz = "GMT") though a better way to do it would be to save the datetimes in Excel as text, and then use `as.POSIXct(..., format = ...)` on the text field once read into R. Something like: times <- c("7/20/2021 13:30", "7/20/2021 13:40") as.POSIXlt(times, format = "%m/%d/%Y %H:%M", tz = "GMT") On Wed, Jul 21, 2021 at 6:49 PM Shawn Way <sway at empowerpharmacy.com> wrote:> I've usually had good luck with this, but something is not working well. > I have two datetimes in excel > > 7/20/21 13:30 > 7/20/21 13:40 > > And when I convert these to excel's normal storage schema, I get the > following: > > 42935.5625 > 42935.56944 > > Just try to convert this to a POSIX class gives me issues. > > > dt <- c(42935.5625,42935.5694444444) > > > as.POSIXct(dt,origin="1899-12-30 00:00:00",tz="GMT") > > [1] "1899-12-30 11:55:36 GMT" "1899-12-30 11:55:36 GMT" > > As you can see, there is a world of difference here. I've tried any > number of solutions such as lubridate, etc and I get the same result > > > as_datetime(dt,origin="1899-12-30 00:00:00") > > [1] "1899-12-30 11:55:36 UTC" "1899-12-30 11:55:36 UTC" > > Any ideas about what I'm doing wrong? > > > Shawn Way > > ______________________________________________ > 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]]
Hi Maybe I am completely wrong but when you import Excel datetimes the result is character, which can be converted to date format quite simply by strptime In case of numerisc format you need to convert date number to seconds. But you could have problems with your OS as on my system I need to set origin to 1904 nstead of 1900 suggested by Microsoft. as.POSIXct(dt*24*60*60,origin="1904-1-1 00:00:00",tz="GMT") [1] "2021-07-20 13:30:00 GMT" "2021-07-20 13:39:59 GMT">Cheers Petr> -----Original Message----- > From: R-help <r-help-bounces at r-project.org> On Behalf Of Shawn Way > Sent: Wednesday, July 21, 2021 9:23 PM > To: r-help at r-project.org > Subject: [R] Help with Converting Excel Times to R > > I've usually had good luck with this, but something is not working well.I have> two datetimes in excel > > 7/20/21 13:30 > 7/20/21 13:40 > > And when I convert these to excel's normal storage schema, I get the > following: > > 42935.5625 > 42935.56944 > > Just try to convert this to a POSIX class gives me issues. > > > dt <- c(42935.5625,42935.5694444444) > > > as.POSIXct(dt,origin="1899-12-30 00:00:00",tz="GMT") > > [1] "1899-12-30 11:55:36 GMT" "1899-12-30 11:55:36 GMT" > > As you can see, there is a world of difference here. I've tried anynumber of> solutions such as lubridate, etc and I get the same result > > > as_datetime(dt,origin="1899-12-30 00:00:00") > > [1] "1899-12-30 11:55:36 UTC" "1899-12-30 11:55:36 UTC" > > Any ideas about what I'm doing wrong? > > > Shawn Way > > ______________________________________________ > 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.