The YEARFRAC function has an optional argument 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 summarized available options:
Note that basis 0 (the default) and basis 4 both operate based on a 360 day year, but they handle last day of 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.
If you want to calculated the number of years between two dates, you can use the YEARFRAC function, which will return a decimal number representing the fraction of a year between two dates. Here are a few examples of...
If you need to calculate the percent remaining in a year, based on a given date, you do so with a formula based on the YEARFRAC function.
In the example, the formula in C5, which calculates percentage of year remaining...
If you need to calculate a person's age from their birth date, you can do so with the YEARFRAC, INT, and TODAY functions. In the generic version of the formula above, birthdate is the person's birthday with year, and...
The Excel DAYS360 function returns the number of days between two dates based on a 360-day year. Calculations based on a 360-day year comes from certain accounting calculations where all 12 months are considered to have 30 days.
The Excel DATEDIF function returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. For reasons unknown, it is only documented in Excel...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.