Whole course: lesson 1 | lesson 2 | lesson 3 | lesson 4 | lesson 5
In the previous lessons, we learned how to create and display dates and times in Excel. Today is the final lesson in this series. You will learn about functions that allow you to perform calculations on dates in Excel, such as calculating the number of working days between two dates. In total, we will consider 4 such functions.
DATAMES()
Returns the ordinal number of a date that is behind the given date by a certain number of months forward or backward. DATEMES has only two arguments: the start date and the number of months. If the second argument (number of months) is a positive number, then the function will return the future date relative to the start date, and if negative, then the past one.
As you can see, a function DATEMES returned serial numbers of dates, while no automatic change in the number format occurred. To see the dates themselves, apply the format to cells C2 and C3 date:
EOMONTH()
Returns the ordinal number of the last day of the month that is behind the specified date by a certain number of months forward or backward. In terms of syntax, the function EOMONTH very similar to DATEMES and has exactly the same arguments:
To see the dates themselves, apply the format to cells C2 and C3 date:
WORKDAY()
Returns the ordinal number of a date that is ahead or backward from the given one by a certain number of working days. WORKDAY has two required arguments: the start date and the number of working days. If the second argument (the number of working days) is a positive number, then the function will return the future date relative to the given one, and if negative, then the past one.
To see the dates themselves, apply the format to cells C2 and C3 date:
It should be noted that non-working days are weekends and days defined as public holidays. For function WORKDAY Weekends are Saturday and Sunday. Excel itself cannot determine which days to consider holidays, therefore it offers a third optional argument, where you can specify everything yourself.
If in your case the days off are different from the classic Saturdays and Sundays, then you can always use the function WORKDAY.INTERNATIONAL. In fact, this is an extended version of the function. WORKDAY, which allows you to set the number of days off in a week and which days they fall on. Function WORKDAY.INTERNATIONAL first appeared in Excel 2010.
CLEAR()
Returns the number of business days between two dates. Holidays and weekends are not included in this amount. Function PURE WORKERS is the opposite of the function WORKDAY, so once again describe the purpose of the function arguments is not worth it.
As you can see, the result of the function calculation was 63 working days. If we simply subtracted the start date from the end date, we would get 89 days.
If in your case the days off are different from the classic Saturdays and Sundays, then you can always use the function CHISTRABDNI. BETWEEN. In fact, this is an extended version of the function. PURE WORKERS, which allows you to set the number of days off in a week and which days they fall on. Function CHISTRABDNI. BETWEEN first appeared in Excel 2010.
As you can see, everything is quite simple!
On this beautiful note, we end the last lesson of this series. This information should be enough for you to feel quite confident when working with dates and times in Excel. Now your main task is to put this knowledge into practice. All the best to you and success in learning Excel!