10 great examples to master date functions in Excel (part 2)

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.

Basis	Day count basis
0 or omitted	US (NASD) 30 days per month/360 days per year
1	Actual days per month / actual days per year
2	Actual days per month / 360 days per year
3	Actual days per month/ 365 days per year
4	European 30 days per month / 360 days per year

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