If dates between formula

[oddsocks]

Hello,

I'm new to Excel so please forgive me if I don't make sense.

I'm trying to show if a date is between a and b it shows c but if it's between d and e it shows f and so on. E.g. if between the start and end date then fees are due the 14th of the middle month of the next quarter.

I need to show

Quarter Start	Quarter End	Quarter Due
01/01/2022	31/03/2022	14/05/2022
01/04/2022	30/06/2022	14/08/2022
01/07/2022	30/09/2022	14/11/2022
01/10/2022	31/12/2022	14/02/2023

Thank you in advance!

 

[6StringJazzer]

Are you trying to use this data to determine due dates for some other data that you're not showing us? Like if you have the date 03/05/2022 you want to return 14/08/2022?

If that is what you want then this formula would give you the due date of any arbitrary date:

=DATE(YEAR(A1),INT((MONTH(A1)+2)/3)*3+2,14)

This uses an algorithm instead of your data so it will work for any date in any year. The due date is assumed to be the 14th day in the second month after the end of the calendar quarter.

 

[oddsocks]

Are you trying to use this data to determine due dates for some other data that you're not showing us? Like if you have the date 03/05/2022 you want to return 14/08/2022?

If that is what you want then this formula would give you the due date of any arbitrary date:

=DATE(YEAR(A1),INT((MONTH(A1)+2)/3)*3+2,14)

This uses an algorithm instead of your data so it will work for any date in any year. The due date is assumed to be the 14th day in the second month after the end of the calendar quarter.

Yes this sounds right! I will try it tomorrow thank you!

 

[oddsocks]

It worked thank you so much :D

 

[6StringJazzer]

You're welcome!

Just a hint for the future: Your question would have been easier to understand if you had added sample input data and sample results that you want.

 

[oddsocks]

You're welcome!

Just a hint for the future: Your question would have been easier to understand if you had added sample input data and sample results that you want.

Yeah my bad I was tired when I posted lol but will make sure it's clear in future