In Excel 2013, you can enter dates and then add or subtract them much the same as any other number. Here are four common formulas for calculating dates in Excel, as well as a separate system that is more appropriate for accountants using a 360-day year.
Calculating Today's Date
Type =TODAY() in an empty cell. All formulas require the parentheses, however in the Today formula, they must remain empty.
Video of the Day
Press Enter to have Excel calculate the formula. The calculated value, today's date, is visible in the cell. Double-click the cell to see the formula, or single-click the cell to view the formula in the Formula field above the worksheet.
Change the date formatting as needed by clicking the Home tab while the cell containing the date is still selected and then clicking the small Arrow in the bottom of the Number group in the ribbon. Excel offers several formats, including numerical dates, text dates, and combinations of numbers and text, as well as the date and time.
Calculating a Future Date
Click the cell in which you want a future date to appear. Type = and then click the cell containing the current date. Type + and then the number of days you want in the future, such as =B2+10.
Another way to enter a future date based on today's date is to type =TODAY()+10 in the cell.
Press Enter. Excel calculates the future date based on today's date.
Calculating a Past Date
Click the cell in which you want a past date to appear. Type = and click the cell containing today's date. Type - and the number of days you want to subtract. To subtract five days from cell B2, the formula is =B2-5.
Press Enter. Excel calculates the formula to give you the past date.
Calculating Days Between Dates
Click the cell in which you want the difference in dates to appear. Type =, click the later date, type - and then click the earlier date. In this example, the formula is =B4-B6.
Calculating Days Between Dates for Accountants
Many organizations base their finances on a 12 30-day months, making each year 360 days. If this applies to your organization, Microsoft recommends using Excel's DAYS360 formula to calculate the difference between days. Using this formula requires that dates be entered using the DATE formula.
Enter two dates using the DATE formula. Type =DATE() and then type the date inside the parentheses using the year, month and date, separated by commas, such as =DATE(2015,7,29).
In addition to the DATE formula, you can use the TODAY formula. Each time you open the spreadsheet, Excel will recalculate the day's date and adjust the dependent cells accordingly.
Select the cell in which you want the difference in dates to appear. Click the Home tab and then change the Number menu to state Number, rather than Date, Percentage or any other inappropriate format.
Type the =DAYS360() formula. Click inside the parentheses, and then click the earlier date's cell. Type a comma and click the later date's cell. In this example, the formula is =DAYS360(B2,B4).
Press Enter to have Excel calculate the difference in dates based on a 360-day year. You can increase or decrease the number of decimals as needed by clicking the appropriate icons in the Number group of the Home ribbon.