It’s beneficial and useful to display the percent of the year that has elapsed and what percent remains in Excel under some situation.
It’s a small challenge if you do not know. Actually Excel is already embedded with a solution, a formula of YEARFRAC function saves you.
The YEARFRAC function requires a start date and an end date, with these two dates filled, it calculates the fraction of the year representing the number of days between the start date and end date. There is an optional argument called “basis” in the YEARFRAC function, it controls how days are counted when computing fractional years. The default behavior of this argument is to calculate days based on a 360-day /Year, and every month is considered to have 30 days, you can view the function syntax and explanation on Microsoft.
=YEARFRAC(start_date, end_date)
To get the percent of the year that remains, you can achieve it simply by subtract 1 from the YEARFRAC function.
=1-YEARFRAC(start_date, end_date)
Another problem when using Excel is calculating the calendar quarter for a date. there is no built-in function to this requirement, so you have to create a function by yourself.
Fortunately here is a ready-made formula for the calculation.
=ROUNDUP(MONTH(date)/3, 0)
The secret is a simple math, here dividing the month number for a given date by 3 and then rounding that number up to the nearest integer.
If you are in a company making a Excel spreadsheet, and stuck with a solution for calculating the fiscal quarter for a given date, you can using the above formula. The problem arises if the fiscal year of your company does not starts in January, but in October or April. you can not simply use the ROUNDUP function.
Here is a clever fomula converting a date into fiscal quarter which starts from April:
CHOOSE(MONTH(date),4,4,4,1,1,1,2,2,2,3,3,3) //starts from April
CHOOSE(MONTH(date),2,2,2,3,3,3,4,4,4,1,1,1) //starts from October
The CHOOSE function returns an answer from a list of choices based on a position number , the first argument is the index number from 1, it determines which of the next arguments is returned.
