Hello Excellers, time for another Formula Friday. Today let’s look at calculating the percentage of the year that is completed and that is remaining really easily using the YEARFRAC function in Excel.
This figure can be used as a standalone number or calculation for the audience of your report, or used as part of another calculation in your report.
Syntax of YEARFRAC
YEARFRAC(start_date, end_date, [basis])
Where
- Start_date this is required. A start date.
- End_date this is required. An end date.
- Basis Optional. The type of day count basis to use.
It’s an easy formula, as it simply calculates the fraction of the year representing the of days between the start date and end date.
Lets take a look at an example and calculate how much of the year is completed using today’s date and assuming that year start is 01/01/2016.
Equally we can also calculate the percentage of the that remains, by tweaking the formula, all we need to do is subtract 1 from the YEARFRAC formula.
As we can use our own start and end date, you can easily make your start date whatever you want. Maybe your business year end date is 30 October 2016.??
Download the Formula Friday example Files.
[wpdm_package id=’10703′]
Dont forget to sign up to the Excel at Excel Newsletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.
