Excel tips: how to work with dates and days

You will need to use a date format on the formula cells to ensure they correctly display the calculated date.

Months

Adding months is a common requirement in Excel, whether it be for budgets, forecasts or lease calculations. Let’s review two functions that can add months to a date.

First of the month

When working with months, especially in reports, budgets or forecasts, I recommend using the first of the month to refer to the month, to make calculations more straightforward.

To add months to a date, the easiest function to use is the EDATE function. In Figure 1, column C uses the EDATE function to take the date in column A and add the number of months from column B. Column D shows the formula from column C. The EDATE function has two arguments. The first is the start date and the second is the number of months, as a whole number, to add to the start date. If you use a decimal number, the decimal part will be ignored, leaving the remaining whole number. A decimal will not be rounded. If you use a negative number, it will deduct months from the start date.

For all dates before the 28th of the month, the EDATE function is the best way to add months to a date.

End of the month

Problems can arise when performing calculations on, or near, the end of the month date. When you add months to an end of month date, you may end up with a date that isn’t the end of the month of the future month, as in Figure 1, row 4.

On the other hand, Figure 1, row 7 has the correct end of month date, because the future end of month date (28) is less than the start end of month date (31). The issue arises when the start date’s end of month day is less than the calculated month’s end of month day, as in row 4.

If you need to go from one month end date to another, you need to use the EOMONTH function. Examples are shown in Figure 1, column E, and the formulas are displayed in column F.

The EOMONTH function has the same two arguments as EDATE, but it always returns the last day of the month for the calculated month.

You can add or deduct days by doing a basic calculation, as shown in Figure 2.

The formulas in Figure 2 perform exclusive calculations. This means days are added to the start date.

You may want to perform inclusive calculations, where the start date is included as one of the days to be added. For example, if a task takes two days to complete, and I start the task on 1/1/2022, then the task should be completed by 2/1/2022, not3/1/22. To perform inclusive calculations, simply deduct one from the exclusive calculation (=B2+A2-1).

Working days

The previous example does not take into account working days. The date of 1/1/2022 is a Saturday, the next day is a Sunday, and the day after that is a public holiday. If you needed to calculate two working days from 1/1/2022, then the task would finish on Wednesday, 5 January.

Excel has functions that can handle this type of calculation. The WORKDAY and WORKDAY.INTL functions allow you to add working days to a date and find the ending date. The companion video to this article covers the WORKDAY.INTL function in detail.

The WORKDAY function treats Saturday and Sunday as the weekend.

Figure 3 has a table showing the results of the WORKDAY function.

The WORKDAY function has three arguments, where the third is optional. The first argument is a start date. The second argument is the number of whole workdays to add to the start date. The third argument is a range of dates to be treated as holidays and excluded from the days available to perform the task.

The results in column C ignore the weekend. This is basically doing a weekdays calculation rather than a working days calculation. The results in column E take into account the two January public holidays listed in the range H2:H3. When performing calculations, you would include all the public holiday dates for the period involved.

WORKDAY function calculations are exclusive. The start date, if it is a working day, is not included as one of the working days.

Working days between dates

Let’s say you have a start date and an end date, and you want to calculate the number of working days between them. The NETWORKDAYS and NETWORKDAYS.INTL functions can calculate that, with some examples shown in Figure 4.

The NETWORKDAYS function’s first argument is the start date. The second argument is the end date. The calculation is exclusive and doesn’t count the start date as one of the days. To make it inclusive, add one to the NETWORDAYS result.

Column C in Figure 4 does the calculation and includes holidays as workdays. Column E in Figure 4 excludes holidays from possible working days.

Last working day of the month

You may need to know the last working day of the month. To calculate that, we can combine two of the functions. Figure 5 shows the formulas and their results.

By using zero in the EOMONTH function, we find the end of the calendar month of the date in column A. We then add one to that date to get the first day of the next month. That first day is the start date for the WORKDAY function. We then deduct one from that start date to find the last workday in the previous month. The calculation uses the holidays (Western Australian) listed in the range E2:E7.

The companion video (and Excel file) to this article includes the WORKDAY.INTL and NETWORKDAYS. INTL functions that have an extra argument to select the days to treat as the weekend.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected]