Google Sheets – How to Calculate the Percentage of a Year Up to a Date

google sheets

Given a date, I need to calculate the percentage of its position in the year. So that 31-12-2015 would return 1, or 100%, because it's the 365th day of 365 days in that year. And 1-1-2015 would return 0,274% (1/365).

I have a long formula solution

=MINUS(
  A1;
  DATE(YEAR(A1)-1;12;31)
) / MINUS(
  DATE(YEAR(A1);1;1);
  DATE(YEAR(A1)+1;1;1)
)

Basically it counts the day of the year of the date given in A1 and divides that by the number of days in that year.

Is there a way to do this with a shorter formula?

Best Answer

Here is a shorter formula that is equivalent to yours, using yearfrac: 

=yearfrac(date(year(A1), 1, 1), A1, 1)

However, the formula you have is inconsistent with your description, since it returns 0 for 1-1-2015, representing that at the beginning of that day, no time has elapsed since the beginning of the year. To have 1/365 for 1-1-2015 and 1 for 12-31-2015, add 1 to the date, to calculate each day's end:

=yearfrac(date(year(A1), 1, 1), A1+1, 1)
Related Topic