I hope you are all well.
I have a question. I have a list with many payments listed on column A with dates (format day/month/year) in column B and I would like to use a formula which says something like
IF= A1 = Q2, "pay", "don't pay" , IF= A2 = Q2, "pay", "don't pay"...
Q2 means quarter 2
Could I define names for dates? For example Q2 would be 01/04/2021 - 30/06/2021 so all the dates within that range would be named Q2.
Best regards
CodePudding user response：
This solution does not provide names for dates but it might meet your needs:
Make sure column A is formatted as a date, then use this formula to get the quarter from the month (this array allows you to set Q1 if it is not the same as calendar quarters):
="Q"&CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4)
Then test the value of this column:
=if(C1="Q1", "Pay", "Do not Pay")
You could also create a cell at the top of your spreadsheet and name it current_quarter. Then you would type in the current quarter "Q1", "Q2", ... and your formula would be
=if(C1= current_quarter, "Pay", "Do Not Pay")
CodePudding user response：
You are using standard calendar month quarters, so we can get the quarter number easily by dividing and rounding up.
=ROUNDUP(MONTH(A1)/3,0)
You can then use this number in your IF function.
=IF(ROUNDUP(MONTH(A1)/3,0)=2,"Pay","Don't Pay")
CodePudding user response：
You may try anyone of the approaches as explained below, the first one is using ROUNDUP & MONTH Function and the other one is using DEFINED NAMES.
First Approach ---> Using ROUNDUP & MONTH FUNCTION
• Formula used in cell B3
="Q"&ROUNDUP(MONTH(A3)/3,0)
• Formula used in cell C3
=IF("Q"&ROUNDUP(MONTH(A3)/3,0)="Q2","Pay","Don't Pay")
So you can see i have used two columns in the first approach just to make it understandable, therefore just wrap the formula in cell B3 within an IF logic as shown in cell C3 to get the desired output.
Second Approach --> Using DEFINED NAMES & SUMPRODUCT FUNCTION
• Formula used in Defined Names
=ROW(INDIRECT(--TEXT("01-04-2021","dd/mm/yyyy")&":"&--TEXT("30-06-2021","dd/mm/yyyy")))
So you can see I have Defined the Quarter 2 as _Q2 and the reason is a name must either begin with a letter, underscore (_), or backslash (). If a name begins with anything else, Excel will throws an error.
Therefore the formula used in cell D3
=SUMPRODUCT((A3>=_Q2)*(A3<=_Q2))
The above formula creates an array of TRUE's & FALSE's and multiplies to return the corresponding values.
Now the above formula when wrapped within an IF Function it gives us the requisite output as desired,
Formula used in cell E3 (Same it can be done in one cell, to make it understandable i have used two columns)
=IF(SUMPRODUCT((A3>=_Q2)*(A3<=_Q2))=1,"Pay","Don't Pay")
So this is how you can used a Defined names for dates in excel and then use the same within a formula.
