2

I have an excel file with a column of date info. These are the dates that people took a survey, and are formatted like "1/20/2017 2:22:34 PM" as downloaded from Qualtrics.

But when I import this file to R using read_excel, each date automatically gets converted to a string like "43122.82".

Ultimately I want these dates to be Date types. I'd appreciate any help.

Improve this question
5
  • 1
    Have you tried using the col_types argument? Commented May 17, 2018 at 3:09
  • Anything else in that column that's not a date? Commented May 17, 2018 at 3:11
  • Have you looked at the qualtRics package? If you have API access on your Qualtrics account, you can circumvent this issue by either loading data from Qualtrics into R directly. Commented May 17, 2018 at 4:29
  • Make your life easier and reformat the date column in Excel ! This should be a 1-click action. Commented May 17, 2018 at 6:14
  • using openxlsx::read.xlsx with detectDates argument Commented May 17, 2018 at 6:49

1 Answer 1

Reset to default
2

You could either try using the col_types argument when reading the file, or do the conversion afterwards:

data$datecol <- as.Date(data$datecol, origin = "1900-01-01")

or if it's, as you say, a string...

data$datecol <- as.Date(as.numeric(data$datecol), origin = "1900-01-01")

Better even, to keep the time, try:

library(lubridate) data$datecol <- as_datetime(as.numeric(data$datecol)*3600*24, origin='1900-01-01')
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I think the origin should be '1899-12-30'. Excel has a bug of considering 29th Feb 1900 as a valid date, It was however invalid because it wasn't a leap year. I am also not very sure about it though. Thanks
Interesting... I also read somewhere that older versions of Excel use 1904-01-01 as a starting point... maybe not unrelated!
Joel Spolsky tells a great story about this: joelonsoftware.com/2006/06/16/my-first-billg-review
@Aaron Nice! Everything is indeed interconnected.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.