Dates with Excel.

Sorry to disappoint, but this is not about romantic evenings with a spreadsheet — it’s about calculations Excel can make with dates, such as computing a person’s age or the due date of an invoice. It’s easy once you know how it works.

The Background Story. The secret to calculating with dates is understanding what goes on in the background. When we type something Excel recognizes as a date, 5/1/2016 for example, two things occur: (1) Excel records a serial number for that date and (2) it formats the cell to display the date corresponding to the serial number.

The serial number simply counts days starting with January 1, 1900. In this system, May 1, 2016 is day 42,491. The problem is we don’t think of 42,491 as a date so Excel turns on a number format that displays it as something we recognize, such as 5/1/2016.

Try it yourself: Enter a date. Then right-click on the cell and choose Format Cells from the menu. Click the “Number” tab at the top of the dialogue box and you’ll see that a date format has been applied. Click General in the category list on the left and the sample will show the date’s serial number.

Date Calcu­lations. Excel does date arithmetic by adding and subtracting these serial numbers. For example, to compute the number of days from May 1, 2016 until Christmas 2016, Excel finds the serial number for each date and computes the difference. Similarly to calculate 30 days from May 1, Excel adds 30 to the serial number for May 1 and displays the result as a date (May 31).

Date Functions. Sometimes we want to do more than simply find date intervals. For example, we may want to extract the month or year component from a date, or know what day of the week it is. That’s where functions come in. Here are a few simple ones:

MONTH. Extracts the month number from a date.

YEAR. Pulls the year from a date.

WEEKDAY. Identifies by a number from 0 to 7 the day of the week for a given date. Options let us choose whether the week starts on Sunday or Monday and whether the lowest value is 0 or 1.

TODAY. Looks up the current date from the computer’s clock and inserts it into a formula.

Specialized Date Functions. Certain date calculations can’t be done with arithmetic alone, for example calculating the number of workdays between two dates. If we are lucky Excel has a function that will do it for us. Here are a few:¹

NET­WORK­DAYS. Calculates the number of days between two dates excluding Saturdays and Sundays. There is an option to specify additional non-working days, such as holidays (not used in example at right). There is another function called NETWORKDAYS.INTL which lets us specify weekends other than Saturday/Sunday.

DAYS360. For historical reasons interest on certain transactions is computed on the basis of a 360 day year (twelve 30-day months). This function calculates the number of days between two dates using this system.

EOMONTH. Finds the last day of the month that is a specified number of months from the input date. For example, leases typically end on the last day of the month, so a 12-month lease signed on April 27, 2016 will be computed to end on April 30, 2017.

Where Are These Functions? Functions can be typed directly into a formula, but we recommend using the Function Wizard. It lists all available functions, explains what each does and helps make sure things are set up correctly.

Launch the Function Wizard by clicking the fx button either on the Formula Ribbon (larger button in image) or the Formula Bar (smaller button in image). For more about the Function Wizard see More Excel Magic.

Formatting Dates. The display of date serial numbers is controlled by cell formatting not typing.² For example, we can tell Excel to display April 27, 2016 even though 4/27/16 is typed.

These settings are in the Format Cells dialogue box. To use it, select the cells to format, right-click and choose Format Cells from the menu. Go to the “Number” tab and click “Date” in the category list. Choices are displayed on the right. The sample will preview how each will display your data.

Dates Before 1900. Not an issue for most people, but archivists, genealogists and historians work with these dates all the time. How does Excel’s January 1, 1900 limitation affect these applications?

Not a problem if we simply want to record the information as text. Enter 7/4/1776 and Excel treats it the same as if we type the words Independence Day, it will not attempt to convert the digits to a serial number.

But that’s not good enough if we want to sort or calculate with these dates. For that we need to create custom functions or macros. Examples and code can be found on-line.³

Caveat. For those whose work takes them overseas, it’s important to recognize that calendar standardization is fairly new. For example, our Gregorian calendar was only adopted by Russia in 1918 and Greece in 1923, so older dates from those countries need to be adjusted. Timeanddate.com is a good resource for such questions.

* * * * *

Who knows, you might find yourself having fun on dates with Excel. And don’t worry, they will be strictly Dutch treat and with no morning-after regrets.

-----

1. Microsoft has a list of Date functions on its Office.com support site.
2. Sometimes Excel automatically applies a certain date format based on what is typed. If it’s not the desired format, just select the cells and choose the intended setting from Format Cells (explained above).
3. Extensive discussion in Charley Kyd, How to Work with Dates Before 1900 in Excel, at exceluser.com. Microsoft’s web site provides VBA for computing age in years for dates prior to 1900, How to calculate ages before 1/1/1900 in Excel.

Date calculations are covered in our Advanced Excel class.

Visit our Excel Productivity Guides page for more helpful articles.

This article originally appeared in our free twice-monthly e-mail newsletter. To receive future issues, please add your name to the subscription list.

Want to learn more? We offer seven different Excel classes: Mastering Excel, Advanced Excel, Excel Charts, Excel Dashboards, Macros and Pivot Tables and Power Pivot. Click any class name for a detailed description.

We can also create a private Personalized Excel Class for your group, or provide individual training or project consulting services.