Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-07-2020, 10:56 PM
Marcia's Avatar
Marcia Marcia is offline Formula if today is nth month of quarter Windows 7 32bit Formula if today is nth month of quarter Office 2013
Expert
Formula if today is nth month of quarter
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default 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.
Reply With Quote
  #2  
Old 09-07-2020, 11:33 PM
ArviLaanemets ArviLaanemets is offline Formula if today is nth month of quarter Windows 8 Formula if today is nth month of quarter Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Code:
=IF(MONTH(TODAY()) = 3*INT((MONTH(TODAY())-1)/3)+n,$F1,"")
where n is quarter month number, you want the formula to return the result for (1, 2, or 3)
Reply With Quote
  #3  
Old 09-07-2020, 11:53 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula if today is nth month of quarter Windows 7 64bit Formula if today is nth month of quarter Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Also
=IF(FLOOR(MONTH(TODAY()),3)=MONTH(TODAY()),$F1,"")
Reply With Quote
  #4  
Old 09-08-2020, 12:00 AM
Marcia's Avatar
Marcia Marcia is offline Formula if today is nth month of quarter Windows 7 32bit Formula if today is nth month of quarter Office 2013
Expert
Formula if today is nth month of quarter
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #5  
Old 09-08-2020, 04:48 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula if today is nth month of quarter Windows 7 64bit Formula if today is nth month of quarter Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Quote:
Originally Posted by Marcia View Post
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.

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)
Reply With Quote
  #6  
Old 09-12-2020, 09:51 PM
Marcia's Avatar
Marcia Marcia is offline Formula if today is nth month of quarter Windows 7 32bit Formula if today is nth month of quarter Office 2013
Expert
Formula if today is nth month of quarter
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by ArviLaanemets View Post
Code:
=IF(MONTH(TODAY()) = 3*INT((MONTH(TODAY())-1)/3)+n,$F1,"")
where n is quarter month number, you want the formula to return the result for (1, 2, or 3)
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.
Reply With Quote
  #7  
Old 09-12-2020, 11:21 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula if today is nth month of quarter Windows 7 64bit Formula if today is nth month of quarter Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Could you please tell me what is wrong with my suggested formula?
Reply With Quote
  #8  
Old 09-13-2020, 05:13 AM
Marcia's Avatar
Marcia Marcia is offline Formula if today is nth month of quarter Windows 7 32bit Formula if today is nth month of quarter Office 2013
Expert
Formula if today is nth month of quarter
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Pecoflyer View Post
Could you please tell me what is wrong with my suggested formula?
I was thinking of going back back to your formula later after I learned Arvi's. I changed the givens in the OP by populating E1 to E12 with the months of the year. In A1 to A12, your formula returns the value of $F$1 on January, April, July and October, all the 1st month of every quarter. In B1 to B12, I would like your formula to return the value of F1 when the months are on the second months of the quarters, like Feb, May, Aug and Oct. In C1 to C12, the Value of F1 should be in the Mar, June, Sept and Dec.
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.
Reply With Quote
  #9  
Old 09-13-2020, 09:22 AM
Purfleet Purfleet is offline Formula if today is nth month of quarter Windows 10 Formula if today is nth month of quarter Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Attached Files
File Type: xlsx Formula if today is nth month of quarter_purfleet.xlsx (10.6 KB, 6 views)
Reply With Quote
  #10  
Old 09-13-2020, 06:09 PM
Marcia's Avatar
Marcia Marcia is offline Formula if today is nth month of quarter Windows 7 32bit Formula if today is nth month of quarter Office 2013
Expert
Formula if today is nth month of quarter
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Purfleet View Post
Just my effort, =IF(MOD(MONTH(I2),3)=1,F2,"")
Thanks Purfleet. I replaced the ($I2) to (TODAY() in C2. It returned blank when it should return the value in $F$1
Reply With Quote
  #11  
Old 09-13-2020, 09:44 PM
Purfleet Purfleet is offline Formula if today is nth month of quarter Windows 10 Formula if today is nth month of quarter Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #12  
Old 09-13-2020, 10:28 PM
ArviLaanemets ArviLaanemets is offline Formula if today is nth month of quarter Windows 8 Formula if today is nth month of quarter Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by Marcia View Post
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.
Attached Files
File Type: xlsx ValueForNthMonthOfQuarter.xlsx (8.4 KB, 4 views)
Reply With Quote
  #13  
Old 09-14-2020, 08:32 PM
Marcia's Avatar
Marcia Marcia is offline Formula if today is nth month of quarter Windows 7 32bit Formula if today is nth month of quarter Office 2013
Expert
Formula if today is nth month of quarter
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Attached Files
File Type: xlsx nthMonththisQuarter.xlsx (15.5 KB, 5 views)
Reply With Quote
  #14  
Old 09-15-2020, 03:43 AM
Purfleet Purfleet is offline Formula if today is nth month of quarter Windows 10 Formula if today is nth month of quarter Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Yes sorry, mine should have said change the 1 to a 2 or 0 (not 3)
Attached Files
File Type: xlsx nthMonththisQuarter_purfleet.xlsx (16.2 KB, 6 views)
Reply With Quote
Reply

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
Formula if today is nth month of quarter Format in a formula month and numbers Marcia Excel 10 02-28-2019 05:55 AM
Formula if today is nth month of quarter 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 if today is nth month of quarter Formula For Displaying Exact Days of the Month ddhawks Excel 2 07-12-2016 11:48 PM
Formula if today is nth month of quarter Formula to divide if no of days in month is 28 shilabrow Excel 5 06-26-2014 12:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:13 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft