Date Formulas in Excel

Some of the more common formulas I use on a regular basis are date manuputation formulas. Whether it using the Date() formula or the Month() formula or many others, there are several ways to show data that have dates involved.

Let’s start by taking a look at the Date Formula itself:

Date(Year, Month, Day)

where Year, Month and Day are numeric value or cell references to numeric values. The result returns a serial number for the result; if cell was formatted to general before the formula was entered it will be automatically re-formatted in date format.

Example 1: Date(2007, 1, 5) will return 1/5/2007.
Example 2: Date(2007, 14, 5) will return 2/5/2008.
(Note: if the number for the month is greater than 12 year will be incremented accordingly and the months mod 12 will be returned for the resulting months. The same is true if the days entered are greater than the number of days for that particular month)

Example 3: Date(2007,1,32) will return 2/1/2007

Excel stores dates as sequential serial numbers so they can be used in calculations. For example if 1/1/2007 is formatted as a number, you will see 39083. If you add 2 to that (as in 2 days) the result will be 39085 or 1/3/2007 if formatted as a date again.

Date Formula using Cell References

Most of the time you will using the Date Formula, you will be using it together with cell references. Below are a few examples of how to do this.

Example 1: Date(C2,D2,E2) will return 1/16/2007
Example 2: Date(C4,D4,E4) will return 5/3/2007
Example 3: Date(C4,D4+2,E4+10) will return 7/13/2007

Tricks Using the Date Formula

There are also formulas that will break apart a date, such as 2/1/2007. Using the Day(), Month(), and Year() Formulas, you can create 3 different columns to break apart the date if you like to have summaries of that data by month for example.

Example 1: =Year(“1/1/2007″) returns 2007, which is the same as
Example 2: =Year(Date(2007,1,1)) returns 2007
Example 3: =Year(1/1/2007) returns 1900, date must be entered as a string if inside year formula

Trick #1: Use the Date formula to find the number of days in a month. If, for example, you are trying to find the number days in February 2007, enter the formula as =Day(Date(2007,3,1)-1), the result will be 28.

Trick #2: Use the Date formula to find the last date in a month. If, for example, you are trying to find the last day in March 2002, enter the formula as =Date(2002,4,1)-1, the result will be 3/31/2002.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

More Excel Tips

• Check if a Cell Contains Text
• Sum Values in a Range While Ignoring #N/A
• Summing Across Multiple Sheets
• Find the 2nd Largest Value in a Range
• Quick Way to Check for Duplicates in a Column