This is the second part of the post “10 great examples to help you master date functions in Excel”. In the first part of this post I covered how Excel handles dates and 5 applications of date functions such as: how to calculate number of working dates between two dates see in part 1  and how to get the last day of a month see in part 1. In this second part I’ll cover 5 additional examples of date functions in Excel.

6) Determine the first day of a month:

To determine the first day of a month you can use the function EOMONTH. You just need to calculate the last day of the previous month and add 1 day to that date.

In general, if you want to determine the n^th day of a month, just add n to the last day of the previous month.

7) Add n years to the current date

Remember that the EDATE function adds (or subtract) a number of months to a specified date (start_date).

The syntax is: =EDATE(start_date, months)

Therefore, if you need to add n years to a date, you just add 12*n (12 months in a year) to the starting date.

8) Calculate years between two dates

To calculate the years that have passed between two dates we will use the YEARFRAC function.

YEARFRAC calculates the fraction of a year represented by the number of whole days between two dates.

The syntax for YEARFRAC is: =YEARFRAC(start_date, end_date, basis)

Basis (optional argument): is the type of day count basis to use.

9) Convert a date-like text to date

Sometimes dates are introduced in Excel formatted as text and when you try to use a date function you get an error. To convert a date-like text to a serial number that Excel recognizes as a date we can use the DATEVALUE function.

The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date.

The syntax for DATEVALUE is: =DATEVALUE(date_text)

The DATEVALUE function syntax has the following argument:

DATEVALUE will recognize the most common text strings used to symbolize dates, for example “6/25/2015” or “25-Jun-2015”.

If DATEVALUE returns “#VALUE!” error value if the value of the date_text argument falls outside of the range between January 1, 1900 and December 31, 9999 or Excel cannot identify the text format used. For example:

Excel recognizes the format mm/dd/yy but it doesn’t recognize the format mm.dd.yy. Therefore, we need to substitute the “.” by “/” to make the DATEVALUE function work.

We can accomplish this with the SUBSTITUTE function.

The SUBSTITUTE syntax is: =SUBSTITUTE(text, old_text, new_text, [instance_num])

Text is the text or the reference to a cell containing text for which you want to substitute characters.

Old_text is the text you want to replace.

New_text is the text you want to replace old_text with.

Instance_num (Optional) – Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

10) Determine the Nth Weekday of a month

Examples: Get the first (1st) Monday of a month or the second (2nd)
Wednesday of a month

To determine the Nth weekday of a month you can use the following formula:

=EOMONTH(start_date, -1) + 1 + 7*N – WEEKDAY(DATE(YEAR(start_date),
MONTH(start_date), 8 – weekday number

For the weekend number use 1 for Sunday, 2 for Monday, …, 7 for Saturday

Time to practice!

Download this workbook to play around with the examples and to complete some practice exercises.