Quote:
Originally Posted by Marcia
Arvi, I had been trying to figure out this formula. I listed in column E all the months in a year then changed "TODAY" to E1, E2..... January is 1, February is 2. I can not understand why the formula (=month(E1)=3.....)still returns the value of F1 when January is less than 3. Could you explain its syntax please.
|
Here you go!
The expression INT((MONTH(TODAY())-1)/3) returns 0 for months 1 - 3, 1 for months 4 - 6, 2 for months 7 - 8 and 3 for months 9 - 12;
Multiplying the result of precious expression returns 0, 3, 6 and 9 respectively (end month of previous quarter in case you count december of previous year as 0);
Adding the number of month in quarter you want the result returned for (n) to result of previous expression returns the number of this month in year;
All what remains, is to use IF() to compare month numbers, and return the wanted value when there is a match.