Monday, January 2, 2017

Understanding Times and Dates in Excel

I originally wrote this post for a software company’s blog in 2014. This company was bought out and recently their blog and website have been removed from the web permanently. I am reposting it here for posterity. 

I love the spreadsheet application Microsoft Excel. In fact, it’s my go to application for crime analysis. In spite of all the really wonderful dedicated crime analysis software packages out there, if you had nothing other than Microsoft Office (or Libre Office if your agency is really cash strapped), you could still do a great job of crime analysis. That is, if you understand how to use it.

Excel and it’s open source clone in LibreOffice are capable of some amazingly sophisticated analysis. In this post we’re going to look at how Microsoft Excel calculates dates and times. Temporal analysis or the analysis of dates and times is an important basic skill for crime analysts. Temporal analysis can help you to profile and predict when serial offenders may strike again.

Previously, I covered how to create a Temporal Analysis Heat Map in Excel to help analyze temporal data . A heat map is one way to analyze temporal data. However, it is also possible to use some basic statistics to predict when a serial offender may strike next. I covered this in the post Simple Crime Series Predictions. If we understand how Excel handles times and dates we can automate some of these calculations.

First off, we need to understand how Excel stores data in a cell.

When you type something into a cell, Excel stores more than just text in the cell. It stores formatting information such as text color, type choices such as bold, italic, underline, font or font size. It also stores information that lets Excel know what kind of data it is, such as text, a number such as currency or a percentage and most importantly for temporal analysis if the value is a time or date.

For the most part, Excel does a pretty good job of recognizing when you type a date or time into a cell. If you type 1/1/14 into a cell, it will accurately interpret that as the date January 1, 2014. However, this isn’t always the case. I frequently notice that Excel will have problems if you are exporting data from a database and importing that data into Excel. Most commonly Excel will mistakenly interpret all the values as text even if it looks like 1/1/14 in our previous example.

If Excel knows that the values entered into two cells are both dates, it can perform mathematical calculations on those values. For instance, if you have the date 1/1/2014 in one cell and 1/5/2014 in another, you can write a formula that will subtract the earlier date from the latter and return the result of 4 (days). It can also do the same with times or date/time combinations. The important part is setting the “Format Cells” dialog to the correct type.

To access this dialog you can either select the “Format Cells” command from the context menu on the Windows version of Excel by right clicking your mouse over a cell and drawing down to “Format Cells” or on the Mac version you get this menu by holding down the Control key, then clicking or the even easier way to hold down the Command key and press 1.

The Format Cells dialog looks like this:

Once you’ve got this dialog up, you can then select the Date or Time format that you find appropriate. One caveat is that if you are going to subtract one date from another or one time from another make sure that you apply the correct formatting to the cell the result goes into. For instance if you subtract 1:00 PM from 10:00 PM you are probably expecting 9:00 hours. If you select the format for Minutes and Seconds (00:00.0) you won’t get 540 minutes but instead will see 00:00.0 in the cell. In this case select Hours and Minutes (00:00) and you’ll get the proper result. There is a “Sample Result” displayed on the dialog so you can see what you are likely to get with the various options.

Now going back to the example in the post Simple Crime Series Predictions instead of typing the values for Days Between Hits in manually, we can place a formula in that cell to calculate these Days automatically.

Another thing to watch out for is if you have a mismatch in the value in the cell and the Formatting of the cell. For instance, if you’ve set the Format of the cell to Date and then enter a value that is obviously not a date such as THEFT you’ll get an error from Excel that looks like “#VALUE!” This is Excel’s way of letting you know there is a mismatch between the value you entered and what it expected based on the cell formatting.

Understanding how Excel interprets Times and Dates can help you take advantage of Excel’s ability to calculate them.

No comments:

Post a Comment

I reserve the right to remove defamatory, libelous, inappropriate or otherwise stupid comments. If you are a spammer or are link baiting in the comments, a pox be upon you. The same goes for people trying to sell stuff. Your comment will be deleted without mercy.