I often build models that define the fraction of a year for a given month. For example, January is approx. 8.5% of a calendar year (31/365) and September is approx. 11.5% of a working day’s calendar (30/260). But what happens in situations when you want to calculate the percentage of a year where the days are not the first or last day of the month? This is where Excel’s YEARFRAC formula can help.
The YEARFRAC function calculates the fraction of a year based on the number of whole days between two dates. Maybe you have summer employees and want to determine how much of the calendar year they will be employed or maybe you need to rent some equipment and pay based on an annual rate.
The function has two required parameters and one optional parameter:
=YEARFRAC(start_date, end_date, [basis])
- Start_date (required)
- Microsoft recommends using the DATE function for this value
- End_date (required)
- Microsoft recommends using the DATE function for this value
- Basis (optional)
- This determines the denominator using a series of approaches such as 360 vs 365 and whether you want to include the start date
This function will allow you to quickly calculate decimals which can then be used for various forms of allocations, seasonality, and other types of spreads.
Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!
Read more IBM Planning Analytics Tips & Tricks:
IBM Planning Analytics Tips & Tricks: 21/21/21
IBM Planning Analytics Tips & Tricks: Creating Control Objects from the Modeling Workbench
