Using Excel to Manipulate Date and Time Stamps

This post shows how to use formulas to manipulate date and time data in Microsoft Excel. I demonstrated this technique as part of a presentation that I made at the Wrangling Library Data virtual conference last month.

Systems typically log the activities that they automate, with each transaction getting a date and/or time stamp. In a library context, a date/time stamp might be associated with a DVD checkout, an article download, a website visit, or passage through a security gate. Below is an example of a date/time stamp:

At first glance this might seem to consist simply of two pieces of data: a date and a time. Consider, though, that one can derive other units of information from these two pieces. The date consists of a month, day, and year; it also fell on a particular day of the week and occurred in a certain week of the year. As for the time, it consists of hours, minutes, seconds, and the ante meridiem designation.

Not all of these units of information are insightful in every situation, but the fact is that they’re contained or implied within the date/time stamp. So, how can one easily derive them from the raw date/time stamp? It’s simply a matter of creating formulas that use appropriate functions and reference the cell where the date/time stamp appears.

Let’s say that the date/time stamp is located is cell A1. We can derive other pieces of information by entering formulas such as the following in the adjacent cells:

• =YEAR(A1)

• =MONTH(A1)

• =WEEKDAY(A1)

• =VALUE(HOUR(A1)&":00") [Note: This formula displays the hour bracket in which the transaction occurred.]

When it comes to analyzing transactional data, it’s very helpful to be able to place dates or times into certain categories. For example, if you’re looking at gate traffic, you might want to be able to construct a grid that shows average traffic by the day of week and the hour of the day. To do this, you have to convert the raw dates and times into derivative data points, and you can easily do this with function-based formulas.

If you can see the potential in this, but don’t want to learn how to create formulas yourself, you’re in luck. As a part of my conference presentation, I shared an Excel file that performs date and time manipulation automatically; all you have to do is paste in a column of date/time stamps. The date manipulation results will look like the image below.

Want to learn more about data Analysis and related topics?

Click the buttons below to see relevant entries in my bibliography, SmithFile.