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]).**

Parameter | Description |
---|---|

start_date | This parameter is a valid date serial number that represents the start date. This parameter is required. |

end_date | This parameter is a valid date serial number that represents the end date. This parameter is required. |

basis | The 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.

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.

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.

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.

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_number | This parameter is a valid date serial number that represents the date that the week number will be returned for. This parameter is required. |

return_type | This 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 Value | Week Start Date | System |
---|---|---|

or omitted | Sunday | 1 |

2 | Monday | 1 |

11 | Monday | 1 |

12 | Tuesday | 1 |

13 | Wednesday | 1 |

14 | Thursday | 1 |

15 | Friday | 1 |

16 | Saturday | 1 |

17 | Sunday | 1 |

21 | Monday | 2 |

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

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

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.

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.

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 1^{st} January was a Friday, resulting in the first Thursday of the year being the following week, the 7^{th} 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_date | A 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_date | A 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. |

unit | The 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 Value | Description |
---|---|

Y | The number of complete years in the period. |

M | The number of complete months in the period. |

D | The number of days in the period. |

MD | The difference between the days in start_date and end_date . The months and years are ignored. |

YM | The difference between the number of the months in start_date and end_date . The days and years are ignored |

YD | The 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.

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.

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 15^{th} and the 31^{st}. We achieve this using the following formula.

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.

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 15^{th} day of a month and the 31^{st} 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 |
---|---|

number | The number you want to convert. Required parameter |

form | There 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 Value | Description |
---|---|

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.

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.

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.