1

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?

3

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)
| improve this answer | |
2

Short answer

Besides the use of other functions, like YEARFRAC() as was suggested by Live Forever, usually the way to get the shorter formula is to use a custom function.

Example

A Custom function

/**
 * Calculates the progress of the year for the date provided.
 * 
 * @param {DATE(2015,1,1)} input - The date to calculate the progress of the year.
 * @customfunction
 */
function YEARPROGRESS(adate) {
  if(adate instanceof Date){   
    var start = new Date(adate.getFullYear(), 0, 0);
    var day = Math.floor(adate - start);
    var end = new Date(adate.getFullYear(), 11, 31)
    var days = Math.floor(end - start);
    return day/days;
  } else {
    throw new Error('adate should be a valid date' );
  }
}

Use examples

=YEARPROGRESS(A1)  
=YEARPROGRESS(DATE(2015,1,1))

Use the cell formatting options to display the result as percentage.

Custom function names

If you want to save even more characters, you could name your function as short as a single alphabetic character (A..Z) but this is not a good practice as this could make a bit harder to remember how was named.

From the reference

Naming

In addition to the standard conventions for naming JavaScript functions, be aware of the following:

  • The name of a custom function must be distinct from the names of built-in functions like SUM().
  • The name of a custom function cannot end with an underscore (_), which denotes a private function in Apps Script.
  • The name of a custom function must be declared with the syntax function myFunction(), not var myFunction = new Function().
  • Capitalization does not matter, although the names of spreadsheet functions are traditionally uppercase.

References

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.