How to convert Excel Numeric Dates to Date Data Type in Exploratory?

When you import Excel data into Exploratory, you might have seen that sometimes the date/time data are imported as numeric values.

Let’s say, we’ve got this data in Excel. the column names are representing each month, such as January 2017, February 2017, etc.

After importing it into Exploratory, you might see something like below.

Notice that the columns names are numeric values now. They are actually the internal representation of the date and time information in Excel. And when importing to R they can be presented as the internal values instead of what you see in Excel.

We can convert these numeric values with ‘excel_numeric_to_date’ function from ‘janitor’ package easily. (The janitor package is pre-loaded in Exploratory.)

Transform from Wide to Long

But before converting it, for the above data specifically, each column is representing each month. It looks like a pivot table and we call this type of data format as ‘wide’ format. It’s recommended we transform the ‘wide’ format to ‘long’ format first because it will make subsequent data wrangling operations such as filtering, calculating, etc. and data visualization much easier.

For this, we can use ‘gather’ command, which would transform the data from ‘wide’ to ‘long’ format quickly.

Select the columns you want to ‘gather’ together while pressing ‘Shift Key’, in this case, they are the ones with the numeric value column names. And select ‘Gather’ -> ‘Selected Range’ from the column header menu.

You will see the start column and the end column being already selected in the opened dialog.

You can type the new column names that will be generated for the month names (still the numeric values) and the values. Also, you can set ‘Guess column data type’ parameter to TRUE to automatically set the appropriate data types for the new columns.

Here’s the same data but in the ‘long’ format.

Finally, we can convert the Excel numeric date values to Date information by using ‘excel_numeric_to_date’ function from ‘janitor’ package.

excel_numeric_to_date(date)

Once you get the data in ‘long’ format it becomes much easier to even visualize the data.

Note that this is a fictional data for this post.

I have shared the data / chart / data wrangling steps together as an EDF (Exploratory Data Format) here. You can download and import into your Exploratory Desktop.