Yearfrac functions returns the portion of years between dates. The most recommended format of return is percentage. Like if you take Jan 01, 2009 as start and Jun 30, 2009 as end, YearFrac will return 0.49 which is nothing but 49% of year.
Formula: Year(StartDate, EndDate, Basis)
StartDate: StartDate is date as the name suggest is beginning date which you want to be considered for calculation. Like, if you want to calculate fraction of year starting from day one of year than start date will be 01/01/2009.
EndDate: Similar as above will be last date till which you want to find the fraction of year.
Basis: There are five options available for choose based on which you will receive the results. Each Type is mentioned below.
- 0: US Calendar style where 30 days per month is considered and hence 360 days in a year. Like 30/360. Also, if nothing is provided in basis than by default if consider as 0.
- 1: This will make formula take actual number of days/actual number of days year consist of.
- 2: Actual number of days/360.
- 3: Actual number of days/365.
- 4: Eurpean 30/360
Basis is a most critical parameter which will be be dependant of type of calendar you follow or you want to use for calculation. So, be sure.
Example 1: We will try to calculate YearFrac using different basis. Just to keep the example started I will considered fixed dates 7/6/2009 and 8/6/2009 which has different of 31 days between them.
Click on image to view enlarge.
Example 2: Task is to calculate leaves on pro-data basis. Like in a year you can have only 21 leaves. So, as the days passes how many leaves will accumulate.
Click on image to view enlarge.
Note: in both examples I have formatted Yeafrac as percentage.
If you still have difficulty in using YearFrac function. I would suggest you to download the file with YearFrac Example. Also, I have added on bonus example in this file.
Click Here to download YearFrac
If you are first time visitor I would request you to subscribe our blog via Email. We will send you free tutorials, e-books, Add-ins and macro code.
We assure you knowledge, not SPAM!
0 comments:
Post a Comment