Exploratory Community

Excel: How to import "time" data to Exploratory

#1

Suppose you have an Excel file that contains time data (e.g. 1:00) like the below screenshot.
The “Time” column uses the Excel “time” format and the “text” column uses Excel text format.

By default, if you import these columns into Exploratory, the “Time” column becomes POSIXct data type and the “text” column becomes hms,difftime data type.

So the “text” column seems ok, since it’s already time data. (i.e. hms, difftime data type).
But Time column contains the unwanted Year, Month, and Date (1899-12-31) part. How can I remove the 1899-12-31 and change the data type to hms, difftime like the “text” column?

You can create a calculation with the below script

as.hms(strftime(Time, format="%H:%M:%S"))

From the column header menu on the Time column, select the “Create Calculation (Mutate)”.

And now the 1899-12-31 part is removed from the “Time” column is changed to time data (i.e. hms, difftime data type) and it looks exactly the same as the “text” column.

2 Likes