Yearfrac Function

Purpose

Return value

Syntax

=YEARFRAC(start_date,end_date,[basis])

• start_date - The start date.
• end_date - The end date.
• basis - [optional] The type of day count basis to use (see below).

Using the YEARFRAC function

YEARFRAC returns a decimal number representing years between two dates. For example:

=YEARFRAC("1-Jan-2019","1-Jan-2020") // returns 1
=YEARFRAC("1-Jan-2019","1-Jul-2020") // returns 1.5
=YEARFRAC("1-Jan-2019","1-Jan-2021") // returns 2

Although the generic syntax for YEARFRAC shows the start date followed by the end date, you can provide the dates in any order with the same result. For example:

=YEARFRAC("1-Jan-2000","1-Jan-2019") // returns 19
=YEARFRAC("1-Jan-2019","1-Jan-2000") // returns 19

Basis options

YEARFRAC uses whole days between two dates to calculate the fraction of a year as a decimal number. The YEARFRAC function accepts an optional argument called “basis” that controls how days are counted when computing fractional years. The default behavior is to count days between two dates based on a 360-day year, where all 12 months are considered to have 30 days. The table below summarizes the available options:

Note that basis 0 (the default) and basis 4 both operate based on a 360-day year, but they handle the last day of the month differently. With the US convention, when the start date is the last day of the month, it is set to the 30th day of the same month. When the end date is the last day of the month, and the start date < 30, the end date is set to the 1st of the next month, otherwise the end date is set to the 30th of the same month. With the European convention, start dates and end dates equal to the 31st of a month are set to the 30th of the same month.

Examples

With a start date in cell A1, and an end date in cell B1, the YEARFRAC will return years between the two dates as a decimal number:

=YEARFRAC(A1,B1) // years between two dates 

To get a whole number only (not rounded), you can use the INT function like this:

=INT(YEARFRAC(A1,B1)) // whole number only, discard decimal

To get current age based on a birthdate, you can use a formula like this:

=INT(YEARFRAC(birthdate,TODAY())) // age from birthdate

Note: this formula can sometimes return incorrect results. See this example for more details.

To get the percentage of the current year complete, you can use YEARFRAC like this:

=YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()) // % year complete

Full explanation here .

Purpose

Return value

Syntax

=ADDRESS(row_num,col_num,[abs_num],[a1],[sheet])

• row_num - The row number to use in the cell address.
• col_num - The column number to use in the cell address.
• abs_num - [optional] The address type (i.e. absolute, relative). Defaults to absolute.
• a1 - [optional] The reference style, A1 vs R1C1. Defaults to A1 style.
• sheet - [optional] The name of the worksheet to use. Defaults to current sheet.

Using the ADDRESS function

The ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return a relative, mixed, or absolute reference, and can be used to construct a cell reference inside a formula. Note that ADDRESS returns a reference as a text value . If you want to use this text inside a formula reference, you will need to coerce the text to a proper reference with the INDIRECT function .

Note: ADDRESS is a special purpose function and is not necessary in most formulas. For example, to retrieve a value at a specific row and column location, you can use INDEX and MATCH .

The ADDRESS function takes five arguments: row , column , abs_num , a1 , and sheet_text . Row and column are required, other arguments are optional. The abs_num argument controls whether the address returned is relative , mixed , or absolute , with a default value of 1 for absolute. The a1 argument is a Boolean that toggles between A1 and R1C1 style references with a default value of TRUE for A1 style references. Finally, the sheet_text argument is meant to hold a sheet name that will be prepended to the address.

ABS options

The table below shows the options available for the abs_num argument for returning a relative, mixed, or absolute address.

Examples

Use ADDRESS to create an address from a given row and column number. For example:

=ADDRESS(1,1) // returns $A$1
=ADDRESS(1,1,4) // returns A1
=ADDRESS(100,26,4) // returns Z100
=ADDRESS(1,1,1,FALSE) // R1C1
=ADDRESS(1,1,4,TRUE,"Sheet1") // returns Sheet1!A1