Excel Date Formula

Have you ever entered a project due date into a spreadsheet by manually counting the number of days and typing in the number? If so, then you’ve also experienced the tedium of recalculating those dates by hand and retyping them.

Fortunately, Excel has all the functions to do the date arithmetic for you.

Entering a Date with the Year, Month and Day

To enter a specific date in your spreadsheet use the DATE(year,month,day) function. Entering:

=DATE(2014,12,15)

returns the date December 15, 2014.

Using the Current Date

To enter the current date, use the dynamic TODAY() function. TODAY() does not require any arguments; it always returns the current date.

You can combine this with the DATE() function. If your project is due on November 10, 2014, count the number of days left before the due date by typing, “=DATE(2014,11,10)-TODAY()”.

Remember: Excel updates the value of TODAY() every time it recalculates your spreadsheet. If you want to save the date that you updated a section of your sheet, type in the date instead of using TODAY().

Adding or Subtracting Dates

To add a number of days to a date, or to subtract a number of days from a date, just add or subtract the number of days. You don’t need any special functions.

In this example:

• “=A1+10” returns December 25, 2014, which is ten days after December 15.
• “=A2-3” returns December 7, 2014, which is 3 days before December 10.
• =”A3+365” returns December 15, 2014, which is 365 days after December 15, 2013.

Note that the number that you add or subtract is always the number of days. You cannot substitute months or years.
Similarly, you can subtract two dates to get the number of days between them.

• “=A1-A2” returns 5, because December 10 is five days before December 15.
• “=A1-A3” returns 365, because December 15, 2013 is one year (365 days) before December 15, 2014.
• “=A2-A3” returns 360, because December 15, 2013, is 360 days before December 10, 2014.

Try it yourself. Type a date into a cell, and practice adding and subtracting numbers of days from it to get the expected results.

Adding or Subtracting Months

To add a number of months to a date, use the EDATE(start_date,months) function. “Start_date” is the date to which to add, and “months” is the number of months you wish to add. Continuing with the table above, =EDATE(A1,3)” returns March 15, 2015, which is three months after December 15, 2014.

To subtract a number of months from a date, use the EDATE() function with a negative number of months. “=EDATE(A2,-2)” returns October 10, 2014, which is two months before December 10, 2014.

Putting Date Formulas to Use

The next time you find yourself manually counting days, try using these functions instead. With the help of one or more of these Excel date formulas you can limit human error and enhance your time management. Master date formulas and create a full-featured project management schedule by using the date functions built into Excel.