Nested IF for Quater between two dates

[Chandresh]

HI all,

need a help on nested if formula for calculating the quarter based on start date and end date

follow is the table

Date	Result	Start	End	Output
03-10-2017		01-04-2017	15-06-2017	Q1
		16-06-2017	15-09-2017	Q2
		16-09-2017	15-12-2017	Q3
		16-12-2017	15-03-2018	Q4
		16-03-2018	31-03-2018	Q5

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>

column A is the date for which I need quarter in column B.

Example 03-10-2017 comes between 16-09-2017(start date) and 15-12-2017(end date) then my result in column B should be Q3.

Thanks
Chandresh

 

[Joe4]

What if you start date and end span two quarters?, i.e.
Start Date: 15-03-2018
End Date: 14-04-2018

What would you want to return in that instance?

Also, I see that you have Q5, instead of starting over at Q1.
Does that mean that you have one initial start date for the very first quarter, and for subsequent years, instead of starting over with Q1, you are going to keep incrementing?
That would seem to imply that 01-01-2017 is your initial start date, and you would never have any dates less than that. Is that correct?

 

[Chandresh]

Date	Result	Start	End	Output
03-10-2017		01-04-2017	15-06-2017	Q1
		16-06-2017	15-09-2017	Q2
		16-09-2017	15-12-2017	Q3
		16-12-2017	15-03-2018	Q4
		16-03-2018	31-03-2018	Q5

<tbody>
</tbody>

My start date and end date is constant for each Quarter Example : 01-04-2017 to 15-06-2107 is my Q1, 16-06-2017 to 15-09-2017 is my Q2.

so whatever date comes in between this dates will fall in that bracket.

 

[Joe4]

Sorry, I totally misread your question.

What you want can be done pretty easily using VLOOKUP. Just set up a three column table with Start Date, End Date, and Output, like you have shown above.
For simplicity sake, we can then make this a named range (see: https://www.contextures.com/xlNames01.html). Let's name it "MyDates".

Now, let's say that you have a date in cell A2, and what to see which quarter it falls in. Use this formula:

=VLOOKUP(A2,MyDates,3,True)

 

[Chandresh]

Yes I have arranged the data and used =VLOOKUP(A1,$C$1:$D$5,2,TRUE) its working

01-04-2017	Q1	01-04-2017	Q1
		16-06-2017	Q2
		16-09-2017	Q3
		16-12-2017	Q4
		16-03-2018	Q5

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

thanks for your help