#1
|
||||
|
||||
Formula if today is nth month of quarter
Hi. I would like help in creating the formula in A2 like this:
=IF(Today falls within the first month of today's quarter,$F1,"") Like, today is the 3rd month of this quarter so the formula should return blank. Thank you. |
#2
|
|||
|
|||
Code:
=IF(MONTH(TODAY()) = 3*INT((MONTH(TODAY())-1)/3)+n,$F1,"") |
#3
|
||||
|
||||
Also
=IF(FLOOR(MONTH(TODAY()),3)=MONTH(TODAY()),$F1,"") |
#4
|
||||
|
||||
Thank you. Tried both formulae and they're perfect. It is my first time to use the FLOOR function.
Edit: In the FLOOR formula, I thought I just need to change the 3 to 1 or 2 just like Arvi's. I was wrong so I will use Arvi's solution. Last edited by Marcia; 09-08-2020 at 02:28 AM. |
#5
|
||||
|
||||
Quote:
No, the 3 does not need to be changed, that is the advantage... FLOOR with a significance of 3 will ALWAYS return the first month of the quarter (1,4,7,10) |
#6
|
||||
|
||||
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.
|
#7
|
||||
|
||||
Could you please tell me what is wrong with my suggested formula?
|
#8
|
||||
|
||||
Quote:
Obviously, English is not my first language so I hope I did not make the problem more confusing. I am using my cp, my laptop could not connect with its hotspot so I couldn't attach a file. Thank you. |
#9
|
|||
|
|||
Just my effort, =IF(MOD(MONTH(I2),3)=1,F2,"").
Change the 1 to 2 or 3 if you want it to return the 2nd month or third. Not sure of the layout of the data, so not sure whay you want to return |
#10
|
||||
|
||||
Thanks Purfleet. I replaced the ($I2) to (TODAY() in C2. It returned blank when it should return the value in $F$1
|
#11
|
|||
|
|||
you need to attach examples otherwise i can only guess what one you have changed
Column A was checking the date in Column I and returning the month in Column F if you wanted the first month of the quart to show, column B did the second month of the quarter and column C did the third quarter. I have no idea on how your data is laid out so i dont know where its going wrong |
#12
|
|||
|
|||
Quote:
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. |
#13
|
||||
|
||||
Thank you for all the help. Finally I could attach the sample sheets. While the problem was solved by Arvi, I am still taking Pecoflyer and Purfleet's formula as possible options and extra lessons.
|
#14
|
|||
|
|||
Yes sorry, mine should have said change the 1 to a 2 or 0 (not 3)
|
Thread Tools | |
Display Modes | |
|
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 |