0

I am trying to read in an excel file using the readXL package with a column of time stamps. For this particular file, they are randomly distributed times, so they make look like 00:01, 00:03, 00:04, 00:08, 00:10, etc. so I need these timestamps to read into R correctly. The time stamps turn into random decimals.

I looked in the excel file (which is outputted from a different program) and it appears the column type within excel is "custom". When I convert that "custom" column to "text", it shows me the decimals that are actually stored and reading into R. Is there a way to load in the timestamps instead of the decimals?

I have already tried to using col_types to make it text or integers, but it is still reading the numbers in as decimals and not the timestamps.

df<-
  readxl::read_xlsx(
    "./Data/LAFLAC_60sec_9999Y1_2019-06-28.xlsx",
    range = cell_cols("J:CE"),
    col_names = T
  )
Andy
  • 65
  • 1
  • 6
  • Most date/times are stored in either days or seconds from a starting point. I'm going to guess those are actually the number of seconds and you can likely use that to convert it back to a date time. Can you confirm if that's the case?if 00:01 is one minute, then it should be showing as 60 seconds, does that seem like what's happening? – Reeza Jul 18 '19 at 16:27

1 Answers1

1

The decimals are a representation in day after midnight. So 1:00 am is .041667, or 1/24.

In R, you can convert those numbers back into timestamps in a variety of ways.

Try this page for more info https://stackoverflow.com/a/14484019/6912825

ckett
  • 214
  • 1
  • 10