Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 3 down vote accepted

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)
share|improve this answer
    
Thanks. I think I will update my description to match the formula I provided in the question. You can adjust your answer accordingly. The combined effort will help readers focus on what the question and answer is really about. – Christiaan Westerbeek Feb 2 at 22:32
    
Done updating my question – Christiaan Westerbeek Feb 3 at 7:09

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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