How to Calculate a Date in Excel

By David Weedmark

Calculate the day's date, past and future dates using Excel 2013, as well as calculating dates based on a 360-day year.

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

Step 1

Type =TODAY() in an empty cell. All formulas require the parentheses, however in the Today formula, they must remain empty.

Step 2

The formula appears in the formula field.

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.

Step 3

Change the date format as desired.

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

Step 1

Add days to the current 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.

Tip

Another way to enter a future date based on today's date is to type =TODAY()+10 in the cell.

Step 2

Excel calculates the future date.

Press Enter. Excel calculates the future date based on today's date.

Calculating a Past Date

Step 1

Subtract days from the current 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.

Step 2

Excel calculates the past date.

Press Enter. Excel calculates the formula to give you the past date.

Calculating Days Between Dates

Step 1

Subtract the earlier date from the later date.

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.

Step 2

Excel calculates the difference in days.

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.

Step 1

Enter dates 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).

Tip

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.

Step 2

Change the cell Number format to Number.

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.

Step 3

Enter the DAYS360 formula.

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).

Step 4

There are 30 days between the two dates.

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.