10 great examples to master date functions in Excel (Part 1)

There are many ways of working with dates in Excel. In this first part I’ll cover 5 out of 10 great examples to help you master date functions in Excel. Before getting started we need to understand how Excel deals with dates. If you type any date in Excel, for example 06/12/2015, and change the format to General you will see that it shows the number 42167. This number represents the number of days that have passed from December 31^st, 1899 and is called a serial number.

Try it in the other way around, type a 5 in any cell and change the format to date. You should see January 5^th, 1900 because that’s 5 days after December 31^st, 1899. Now that you know that days are just numbers with a special format, let’s start playing with dates:

1) Calculate the number of calendar days between two dates:

Just subtract the dates or use the DAYS function available in Excel 2013 or later.

2) Calculate the net working days between two dates:

a. The NETWORKDAYS function returns the whole number of workdays between two dates. You can exclude the holidays from the calculation.

The syntax is: =NETWORKDAYS(start_date, end_date, [holidays])

holidays (Optional argument): Is a list of holidays to exclude from the calculation of working days. If omitted it will assume that there are no holidays within the date range evaluated.

b. If you want to exclude a custom weekend, for example, only Sundays, you can use the NETWORKDAYS.INTL function available in Excel 2010 or later.

The syntax is: =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

weekend (Optional argument): Is a number that indicates which days should be considered as weekend days. For example, 2 = Weekend days are Sunday and Monday; 11 = Weekend days are only Sundays. [table id=1 /]

c. You can also exclude a custom weekend that is not included in the NETWORKDAYS.INTL options. To do this you can type a seven character string formed by ones (1) for days to include in the weekend and zeros (0) for days to exclude from the weekend. For example to exclude Mondays, Wednesdays, and Fridays you can type “1010100” in the weekend argument. [table id=2 /]

3) Add a number of working days to a date:

To add a number of working days to a date you can use WORKDAY or WORKDAY.INTL. These functions have a syntax very similar to NETWORKDAYS and NETWORKDAYS.INTL.

The syntax is:

=WORKDAY (start_date, days, [holidays])

=WORKDAY.INTL (start_date, days, [weekend], [holidays])

days: is the number of workdays to add to the start date.

WORKDAY.INTL is a function available in Excel 2010 or later.

4) Get last day of month for current, past or future months

To obtain the end of a month you can use the function EOMONTH.

The syntax is: =EOMONTH(start_date, months)

5) Add a number of months to a given date

To add or subtract a number of months to a given date you can use the EDATE function.

The syntax is: =EDATE(start_date, months)

Notice that all the functions showed in this post expect a date serial number in their arguments, however, you can type dates as text as well. For example, you could type =EOMONTH(“05/15/2015”, 0)

Time to practice!

Download this workbook to play around with the examples and to complete some practice exercises. Remember, practice is the mother of all skills! Stay tuned for the second part of this post or subscribe to our email list below to get notified when the second part becomes available.