Formula if today is nth month of quarter

ArviLaanemets · #4 09-13-2020, 10:28 PM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter	PrincessApril	Excel	6	12-06-2019 02:38 PM
Format in a formula month and numbers	Marcia	Excel	10	02-28-2019 05:55 AM
Nested If Statements in Formula Field to Produce the Diffrnc in Years Between Today's & Entered Date	mshu31	Word	1	02-09-2017 02:04 PM
Formula For Displaying Exact Days of the Month	ddhawks	Excel	2	07-12-2016 11:48 PM
Formula to divide if no of days in month is 28	shilabrow	Excel	5	06-26-2014 12:17 AM