We’ve written before about manually entering dates and automatically filling them in Excel, but one of the really powerful features of Excel spreadsheets is date functions, which enable you to perform useful date and time calculations. In this post, we present eight of our favorite functions for manipulating date data.
1. DATEVALUE converts text dates to serial numbers
In Excel, a date is actually a serial number. Excel stores all dates as integers and times as decimal fractions. In this system, the serial number 1 represents January 1, 1900 at 12:00:00 am, and it continues date-by-date to more recent days such as 41010, which represents April 11, 2012 at 12:00:00 am. In other words, the left side of the decimal is the day and the right side is the time.
These numbers allow Excel to work with dates just like it does with numbers. These numbers are typically meant for the machine and are shown in a date or time format with which we’re more familiar. The DATEVALUE function helps us convert them into the format that Excel uses.
This function takes a date value entered as text, and returns a value as a serial number.
For instance, the following formula:
returns the number “41194” which will display as a date if the cell uses a date format.
2. There’s no date like TODAY
The TODAY function returns the serial number of today’s date based on your system clock and does not include the time value. Or more specifically, it returns the serial number for 12:00:00 am today. (The NOW function, in contrast, provides the date and the time, but we’re limiting this post to date functions.)
You can use the TODAY function to find out if today matches a certain date.
The following function finds out if today matches a certain date (in cell A1), and outputs the statement “TRUE” or “FALSE”.
Of course, there are many options other than matching the exact date. You can for instance change the EQUAL (=) sign to GREATER THAN (>), meaning that the function will be false when TODAY is on or before a specified date, and true when TODAY is after.
This concept can be used to create a function that shows when a project has not met its deadline in cell A1:
You can also add days to the equation if necessary. For instance, if you want to follow up a week later on an overdue payment, you could have the following formula as a reminder.
=IF(TODAY()>=A1+7,”Followup with Client”,”Wait for Payment”)
3. Taking Account of Work Days with WORKDAY
There are many situations where it makes more sense to use work days as opposed to calendar days when calculating things such as invoice due dates, expected completion dates, and the number of days of work performed.
Excel’s WORKDAY function can be very useful for making these calculations. This function returns the date a number of working days before or after a specified starting date, excluding weekends and holidays. The WORKDAY function follows this form:
Adding holiday dates is optional, but it can include a range of cells that contain holiday dates, or manually entered date values. The order of holiday dates doesn’t matter.
There is also an INTERNATIONAL WORKDAY function, which allows you to calculate dates based on work weeks other than the typical Monday to Friday work week in the US. It has the following format:
WORKDAY.INTL(start_date, days, [weekend], [holidays])
The weekend field is a number representing options such as only Sunday, and Friday and Saturday, which are represented as numbers.
Note: If you find the WORKDAY and NETWORKDAYS functions don’t work for you, you may need to install and load the Excel Analysis ToolPak add-in.
4. NETWORKDAYS keeps track of workdays
The NETWORKDAYS function calculates the number of whole working days between the start and end date, excluding weekends and holiday dates. It is filled out using the following format:
It’s useful to know how many workdays fall between two days, especially when the dates correspond the start and end of a project. It could also help you tally the number of days an employee worked to determine their employee benefits.
5-7. DAY, MONTH, YEAR
This trio of functions help grab a certain parameter of a date.
- DAY converts a date to the number of its day of the month from 1 to 31. For instance, serial number 41010 (the date for April 11, 2012) is converted to the number 11.
- MONTH converts a date serial number to a month.
- YEAR converts a serial number to a year
Each function can be useful if you want only to find the day of the month (for a recurring monthly payment), or the month or year (to, perhaps, get a broad overview of dates).
8. WEEKDAY shows strengths
WEEKDAY takes a date value and returns the day of the week corresponding to a date. By default, the day of the week is given as an integer ranging from 1 (Sunday) to 7 (Saturday).
Brent, one of our contributors, uses WEEKDAY() to determine the Monday prior to a date for a cashflow forecast divided into weeks like so:
=WEEKDAY(A1) returns the day of the week for the date contained in cell A1. For example, if the date in cell A1 were a Wednesday, it would return the value 4.
=A1-WEEKDAY(A1), will always be the Saturday prior to the date in A1.
=A1-WEEKDAY(A1)+2, is the Monday prior to the date in A1.
These have been our favorite Excel date functions. Do you have any date functions you can’t live without? Please let us know in the comments!
Want more quick tips on using Excel? Be sure to check out our Beezix Quick Reference Card on Excel formulas and functions, and our other cards such as an introduction to Excel, and a quick reference on tables, PivotTable sorting and filtering.