December 3, 2020

YEARFRAC function in Google Sheet

The YEARFRAC function returns a decimal value that represents fractional years between two dates in both Excel & Google Sheet.

=YEARFRAC (start_date, end_date, [basis])

Arguments :

  • start_date – The start date.
  • end_date – The end date.
  • basis – This is the optional argument. We recommend this to leave Blank.  Following is some more detail about this parameter just for knowledge.
    • Default value for Basis in 0 which denotes US convention
    • Basis can be 4 to denote European Convention 
    • Both cases are based on a 360 day year, but they handle the last day of month in different manner. With the US convention(Basis=0), 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 & 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(Basis=4), start dates and end dates equal to the 31st of a month are set to the 30th of the same month.
    • Basis can be 1 which denotes actual/actual
    • Basis can be 2 which denotes actual/360
    • Basis can be 3 which denotes actual/365

In below example in C2 we used YEARFRAC function to get the decimal value that represents fractional years between the dates in A2 & B2. Please note, the order of the start and the end date doesn’t matter.

Now we will see how to get the complete or remaining percent of year in Google Sheet.

If you need to calculate the percent completed/remaining in a year, based on a given date, you can do so with a formula based on the YEARFRAC function.

Example 1 : You can use the below formula to find the percent of year completed.

=YEARFRAC(DATE(YEAR(date),1,1),date)

The YEARFRAC function takes two dates, a start date and an end date, and calculates the time between them in fractional years. Like in this example we have a date in A2. This is the start date. We are using the end date as-is, but generating a start date that equals the first day of the same year with this bit of code:

DATE(YEAR(A2),1,1)

This extracts the year value from the date in A2 and plugs it into the DATE function, along with a 1 for both month and day.

The result is that the YEARFRAC function returns a decimal value which is the fraction of the year between the two dates.

When this value is formatted with the percentage number format, it shows the percent of year complete.

Example 2 : You can use the below formula to find the percent of year Remaining.

=1-YEARFRAC(DATE(YEAR(date),1,1),date

Here in below example to calculate percent of year remaining, just adjust the formula to subtract the fractional year from 1.

Now we will see how to find the age from the date of birth in Google Sheet.

Along with the YEARFRAC function, here we need 2 more functions as follows to get the age from date of birth.

  • INT : This Function is used when you want to get the integer portion of a number.
  • TODAY : This function is used to get the current date. It returns a serial number that represents the current date.

In our below example the date of birth is 22 August 1983 in cell A2. We have used the below formula to calculate the age in cell B2.

=INT(YEARFRAC(A2,TODAY()))

Here the YEARFRAC formula takes the date of birth and the current date (which is returned by the TODAY function) and returns the age in years. It contains the integer as well as the fraction part.

After that the INT function returns only the integer part from it.

Leave a Reply

Your email address will not be published. Required fields are marked *