OverviewTeaching: 10 min
Exercises: 3 minQuestions
What are good approaches for handling dates in spreadsheets?Objectives
Describe how dates are stored and formatted in spreadsheets.
Describe the advantages of alternative date formatting in spreadsheets.
Demonstrate best practices for entering dates in spreadsheets.
Authors:Christie Bahlai, Aleksandra Pawlik
Dates in spreadsheets are stored in a single column. While this seems the most natural way to record dates, it actually is not best practice. A spreadsheet application will display the dates in a seemingly correct way (to a human observer) but how it actually handles and stores the dates may be problematic.
In particular, please remember that functions that are valid for a given spreadsheet program (be it LibreOffice, Microsoft Excel, OpenOffice, Gnumeric, etc.) are usually guaranteed to be compatible only within the same family of products. If you will later need to export the data and need to conserve the timestamps, you are better off handling them using one of the solutions discussed below.
Additionally, Excel can turn things that aren’t dates into dates, for example names or identifiers like MAR1, DEC1, OCT4. So if you’re avoiding the date format overall, it’s easier to identify these issues.
Challenge: pulling month, day and year out of dates
- In the
datestab of your spreadsheet you have the data from 2014 plot 3. There’s a
- Let’s extract month, day and year from the dates to new columns. For this we can use the built in Excel functions
(Make sure the new column is formatted as a number and not as a date.)
You can see that even though you wanted the year to be 2014, your spreadsheet program automatically interpreted it as 2015, the year you entered the data.
Challenge: pulling hour, minute and second out of the current time
Current time and date are best retrieved using the functions
NOW(), which returns the current date and time, and
TODAY(), which returns the current date. The results will be formatted according to your computer’s settings.
1) Extract the year, month and day from the current date and time string returned by the
2) Calculate the current time using
3) Extract the hour, minute and second from the current time using functions
F9to force the spreadsheet to recalculate the
NOW()function, and check that it has been updated.
1) To get the year, type
=YEAR(NOW())into any cell in your spreadsheet. To get the month, type
=MONTH(NOW()). To get the day, type
=NOW()-TODAY()will result in a decimal value that is not easily human parsable to a clock-based time. You will need to use the strategies in the third part of this challenge to convert this decimal value to readable time.
3) To extract the hour, type
=HOUR(NOW()-TODAY())and similarly for minute and second.
Note: Excel is unable to parse dates from before 1899-12-31, and will thus leave these untouched. If you’re mixing historic data from before and after this date, Excel will translate only the post-1900 dates into its internal format, thus resulting in mixed data. If you’re working with historic data, be extremely careful with your dates!
Excel also entertains a second date system, the 1904 date system, as the default in Excel for Macintosh. This system will assign a different serial number than the 1900 date system. Because of this, dates must be checked for accuracy when exporting data from Excel (look for dates that are ~4 years off).
Spreadsheet programs have numerous “useful features” which allow them to handle dates in a variety of ways.
But these “features” often allow ambiguity to creep into your data. Ideally, data should be as unambiguous as possible.
The first thing you need to know is that Excel stores dates as numbers - see the last column in the above figure. Essentially, it counts the days from a default of December 31, 1899, and thus stores July 2, 2014 as the serial number 41822.
(But wait. That’s the default on my version of Excel. We’ll get into how this can introduce problems down the line later in this lesson. )
This serial number thing can actually be useful in some circumstances. By using the above functions we can easily add days, months or years to a given date. Say you had a sampling plan where you needed to sample every thirty seven days. In another cell, you could type:
And it would return
because it understands the date as a number
41822 + 37 = 41859
which Excel interprets as August 8, 2014. It retains the format (for the most
part) of the cell that is being operated upon, (unless you did some sort of
formatting to the cell before, and then all bets are off). Month and year
rollovers are internally tracked and applied.
Note Adding years and months and days is slightly trickier because we need to make sure that we are adding the amount to the correct entity.
As for dates, times are handled in a similar way; seconds can be directly added but to add hour and minutes we need to make sure that we are adding the quantities to the correct entities.
Which brings us to the many different ways Excel provides in how it displays dates. If you refer to the figure above, you’ll see that there are many ways that ambiguity creeps into your data depending on the format you chose when you enter your data, and if you’re not fully aware of which format you’re using, you can end up actually entering your data in a way that Excel will badly misinterpret.
What happens to the dates in the “dates” tab of our workbook if we save this sheet in Excel (in
csvformat) and then open the file in a plain text editor (like TextEdit or Notepad)? What happens to the dates if we then open the
csvfile in Excel?
- Click to the “dates” tab of the workbook and double-click on any of the values in the
Date collectedcolumn. Notice that the dates display with the year 2015.
File -> Save Asin Excel and in the drop down menu for file format select
CSV UTF-8 (Comma delimited) (.csv). Click
- You will see a pop-up that says “This workbook cannot be saved in the selected file format because it contains multiple sheets.” Choose
Save Active Sheet.
- Navigate to the file in your finder application. Right click and select
Open With. Choose a plain text editor application and view the file. Notice that the dates display as month/day without any year information.
- Now right click on the file again and open with Excel. Notice that the dates display with the current year, not 2015.
As you can see, exporting data from Excel and then importing it back into Excel fundamentally changed the data!
You will notice that when exporting into a text-based format (such as CSV), Excel will export its internal date integer instead of a useful value (that is, the dates will be represented as integer numbers). This can potentially lead to problems if you use other software to manipulate the file.
Storing dates in YEAR, MONTH, DAY format helps remove this ambiguity. Let’s look at this issue a bit closer.
For instance this is a spreadsheet representing insect counts that were taken every few days over the summer, and things went something like this:
If Excel was to be believed, this person had been collecting bugs in the future. Now, we have no doubt this person is highly capable, but I believe time travel was beyond even their grasp.
Entering dates in one cell is helpful but due to the fact that the spreadsheet programs may interpret and save the data in different ways (doing that somewhat behind the scenes), there is a better practice.
In dealing with dates in spreadsheets, separate date data into separate fields (day, month, year), which will eliminate any chance of ambiguity.
There is also another option. You can also store dates as year and day of year (DOY). Why? Because depending on your question, this might be what’s useful to you, and there is practically no possibility for ambiguity creeping in.
Statistical models often incorporate year as a factor, or a categorical variable, rather than a numeric variable, to account for year-to-year variation, and DOY can be used to measure the passage of time within a year.
So, can you convert all your dates into DOY format? Well, in Excel, here’s a useful guide:
Another alternative could be to convert the date string
into a single string using the
For example the date
March 24, 2015 17:25:35 would
YYYY: the full year, i.e. 2015
MM: the month, i.e. 03
DD: the day of month, i.e. 24
hh: hour of day, i.e. 17
mm: minutes, i.e. 25
ss: seconds, i.e. 35
Such strings will be correctly sorted in ascending or descending order, and by knowing the format they can then be correctly processed by the receiving software.
Treating dates as multiple pieces of data rather than one makes them easier to handle.