Introduction to Excel Date Functions Part 3

Introduction to Excel Date Functions Part 3 - Twitter

Welcome to the final post in this three-part series of blog post introducing Excel date functions. In this final post, we are going to look at few of the less used, but still very useful date functions such as YEARFRAC and WEEKNUM, the DATEDIF function which maintained for computability reasons and for a bit of fun the ROMAN function. As always, we work through some examples as we learn how these functions work.

If you haven’t worked with date functions before, then I recommend reading part-one and part two in this series of blog posts first.

The YEARFRAC Function

The purpose of the YEARFRAC function is to calculate the fraction of the year between two dates. The value returned is a decimal value representing the fraction of the year to the nearest whole day between a start date and end date.

The YEARFRAC function has an optional parameter called basis, this parameter specifies the type of day count used when calculating the fraction of the year. This may seem a little weird, but there are different ways of counting the number of days in a year. I will explain this in more detail shortly.

The syntax of the YEARFRAC function is YEARFRAC(start_date, end_date, [basis]).

ParameterDescription
start_dateThis parameter is a valid date serial number that represents the start date. This parameter is required.
end_dateThis parameter is a valid date serial number that represents the end date. This parameter is required.
basisThe value of the basis parameter determines the day count basis to be used. This parameter is optional; if it is omitted, the value defaults to 0.

The basis parameter takes a value between 0 and 4 inclusive. The value determines the calendar basis for calculating a fraction of a year. The YEARFRAC function gives you the option of choosing five different calendar types. For more information on the different calendar types, please click this link here.

The following table lists the values you can use with the basis parameter.

Basis Parameter Value Description (Calendar Type)
0 or omitted US (NASD) 30/360.
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

If either the start_date of the end_date is not a valid date serial number, then Excel will return a #VALUE! error. Additionally, if the basis parameter is a value other than 0 – 4, Excel will return a #NUM! error.

Calculating the Fraction of a Year Without the Basis Parameter

In our first example, we are going to calculate the fraction of the year between two dates without using a basis parameter value. To do this, we will use the following formula.

YEARFRAC Formula Example 1

As you can see, the YEARFRAC function has just two parameters, the start_date and the end_date. We insert the formula into cell C2, we use the DATE function to create valid serial numbers in cells A2 and B2 like in the image below.

YEARFRAC Formula Example 1 Spreadsheet

As you can see in the image above, the formula containing the YEARFRAC functions returns the value 0.213888889. This is the fraction of the year between the start date, 1 June, 2016, and the end date, 18 August, 2016 to the nearest day. This is based on a 360-day year when the basis parameter is omitted.

Calculating the Fraction of a Year with the Basis Parameter

In the next example, we are going to calculate the fraction of the year between two dates again. However, this time, we will use the basis parameter value “1”.

As can be seen in the table, the parameter value 1 represents the calendar type “Actual/actual.” This means that the fraction-of-year value is calculated based on a 365-day year unless it is a leap year; then, it is based on a 366-day year. To perform this calculation, we use the following formula.

YEARFRAC Formula Example 2

The YEARFRAC function this time has three parameters, the start_date, the end_date, and the basis parameter value 1. As before, we insert the formula into cell C2, and we use the DATE function to create valid date serial numbers in cells A2 and B2 as in the image below.

YEARFRAC Formula Example 2 Spreadsheet

This time, the YEARFRAC returns the value 0.213114754. This is the fraction of the year between the start date, Wednesday, 1 June 2016, and the end date, Thursday, 18 August 2016, to the nearest day. Additionally, because 2016 is a leap year, it is calculated on a 366-day year.

The WEEKNUM Function

The WEEKNUM function returns the week number for a given date. Each week number of the year is represented by a unique number that ranges from 1 to 54. While there are only 52 weeks in a year, depending on when a new year starts, you could have partial weeks at the beginning and the end of the year.

There are two week-numbering systems recognized by the WEEKNUM function, system 1 and system 2.

  • System 1 – The week containing January 1 is week number one
  • System 2 – The week containing the first Thursday of the year is week number one. This week numbering system is referred to as ISO 8601. For more details on this, click here.

The syntax of the WEEKNUM function is WEEKNUM(serial_number,[return_type]).

Parameter Description
serial_numberThis parameter is a valid date serial number that represents the date that the week number will be returned for. This parameter is required.
return_typeThis parameter determines which day of the week, the week starts on. Please see the table below for a description of the different values that can be used with this parameter. This parameter is optional, and if it is omitted, the value defaults to 1.

The optional return_type parameter takes an integer value based on the following table. Please note that in the table below, there are several values for system one. This is because you can define the week to start on different days in system one.

return_type Parameter ValueWeek Start DateSystem
or omitted Sunday1
2 Monday1
11 Monday1
12 Tuesday1
13 Wednesday1
14 Thursday1
15 Friday1
16 Saturday1
17 Sunday1
21 Monday2

If an invalid value is entered into either the serial number parameter or the return type parameter, you will get a #NUM! error.

Calculating the Week Number of a Year Without a Return Type Parameter

In the first WEEKNUM function example, we are going to calculate the week number for the date Wednesday, 1 June 2016, omitting the optional return_type parameter using the following formula

WEEKNUM Formula Example 1

 

We insert the formula into cell B2. We use the DATE function in cell A2 to create a valid date serial number.

WEEKENUM Formula Example 1 Spreadhseet

As you can see from the image above, the formula returns a value of 23. This is correct, because when the return_type parameter is omitted, the WEEKNUM function assumes that the week starts on Sunday and uses a system one, meaning the first week started on January 1.

Calculating the Week Number of a Year with a Return Type Parameter

For our second WEEKNUM function example, we will calculate the week number for the same date as our first example Wednesday, 1 June 2016. However, this time, we will provide the value 21 for the optional return_type parameter using the following formula.

WEEKNUM Formula Example 2

Again, we insert the formula into cell into cell B2, and we use the DATE function in cell A2 to create a valid date serial number.

WEEKENUM Formula Example 2 Spreadsheet

This time, the formula returns the value 22. This is because when the return_type parameter value is 21, the WEEKNUM function uses system 2, meaning that week number 1 starts on the first Thursday in January. In 2016, the 1st January was a Friday, resulting in the first Thursday of the year being the following week, the 7th January.

Just like before, the WEEKNUM function assumes the week starts on Sunday.

The DATEDIF Function

The DATEDIF function can calculate the number of days, months, and years between two dates. The DATEDIF is only really maintained by Microsoft for backwards compatibility with Lotus 1-2-3. Therefore, I am really only including it here for completeness. I wouldn’t recommend using it in any new spreadsheets.

The syntax of the DATEDIF function is DATEDIF(start_date, end_date, unit).

Parameter Description
start_dateA valid date serial number or a text string that represents a valid date format entered in quotation marks that represents the start date. This parameter is required.
end_dateA valid date serial number or a text string that represents a valid date format entered in quotation marks that represents the start date. This parameter is required.
unitThe value of this parameter determines how the result of the function will be returned. The value has to be entered in quotation marks, for example, " D " for days.

The unit parameter takes one of the six possible options in the table below.

Unit Parameter ValueDescription
YThe number of complete years in the period.
MThe number of complete months in the period.
DThe number of days in the period.
MDThe difference between the days in start_date and end_date . The months and years are ignored.
YMThe difference between the number of the months in start_date and end_date . The days and years are ignored
YDThe difference between the days in start_date and end_date . The years are ignored.

Microsoft highlights that:

“The DATEDIF function returns the number of complete days, months or years. This may give unexpected results when the day/month number of the second date [end_date parameter] is lower than the day/month number of the first date [start_date parameter].”

 Calculating the Number of Days Between Two Dates

In our first example, we will calculate the number of days between two dates. We use a start date of Wednesday, 15 August 2016, and an end date of Saturday, 31 October 2016, applying the following formula.

DATEDIF Formlua Example 1

Because we provided the value “D” for the unit parameter of the DATEDIF function, the formula returns the number of days between the two dates. We insert the formula in cell C2, and we use the DATE function to create valid serial numbers for the start_date and end_date parameters in cells A2 and B2 as in the image below.

DATEDIF Formlua Example 1 Spreadsheet

As you can see from the image above, the  DATEDIF function returns the value 77. This is correct; there are 77 days between the start_date (Wednesday, 15 August 2016) and end_date (Saturday, 31 October 2016) parameters values.

The same result can be achieved using the DAYS function.

Calculating the Number of Days between two Dates, Using the Unit Parameter Value “MD”

In our second example, we calculate the number of days between the same two dates. However, this time, we provide the value “MD” for the unit parameter of the DATEDIF function. Thus, the function will calculate the difference between the day numbers of the two dates, i.e., between 15th and the 31st. We achieve this using the following formula.

DATEDIF Formlua Example 2

Like the previous example, we insert the formula in cell C2 and use the DATE function to create valid serial numbers for the start_date and end_date parameters in cells A2 and B2 like the image below.

DATEDIF Formlua Example 2 Spreadsheet

This time, the formula containing the DATEDIF functions returns the value 16. This is because the DATEDIF function is purely calculating the difference between the day number values from our start_date and end_date parameters. Thus, in this instance, the number of days between 15th day of a month and the 31st day of a month is 16.

The ROMAN Function

Okay, this last one is for a bit of fun but is still useful. The ROMAN function isn’t really a DATE function; it actually converts numbers into Roman numerals.

However, if you ever want to convert the year number to Roman numerals like you sometimes see at the end of TV shows and movies, Excel can help you with that.

The syntax of the ROMAN function is ROMAN(number, [form])

Parameter Description
numberThe number you want to convert. Required parameter
formThere are various types of Roman numeral styles. To access these different styles, the form parameter takes a number between 0 and 4 inclusive, or can be the values TRUE or FALSE . The form parameter is optional.

If a negative number is entered, or a value greater than 3999, then the #VALUE! error is returned.

The following table describes the different values that can be entered for the form parameter.

form Parameter ValueDescription
0 or omitted Classic.
1 More concise.
2 More concise.
3 More concise.
4 Simplified.
TRUE Classic.
FALSE Simplified.

Create a Roman Numeral Year

For now we won’t concern ourselves with the various forms of Roman numerals; we will simply show a year formatted as a Roman numeral.

To achieve this, we use the following formula.

Roman Formlua Example 1

Notice that we have omitted the form parameter. This will force the ROMAN function to use the classical format of Roman numerals. We insert the formula into cell B2, and the year 2016 into cell A2 as shown in the image below.

ROMAN Function Formlua Example Spreadsheet

As can be seen from the image above, the formula containing the ROMAN function returns the roman numeral value MMXVI for the number 2016 in cell A2.

Wrap-up

That’s it for this blog post and this series of posts on Excel’s various date functions. I hope you found this series of blog posts useful. As you can see, there are several formatting and date functions available. If you want to calculate the number of working days between two dates, taking into account holidays, or varying weekend days, Excel has you covered.

If you liked this post, please share it with your friends on Facebook or Twitter.

For other tools and resources for learning more about Excel, check out my resources page.

To get a copy of the example spreadsheet used in this blog post and receive a copy of our newsletter, sign up to our email list below.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit