How to return number of quarters from start date, then return zero after end date passed

[StanLeakup]

Trying to create a formula in cells E3 to G3 that will return the number of quarters between the dates in B3 and C3. For instance, in cell E3, would like for the formula to return "1" as there is one quarter between 1/1/2023 and 3/31/2023. The formula would return "2" in cell F3 as there are two quarters between 1/1/2023 and 7/1/2023. Finally, the formula would return zero in cell G3 as 9/30/2023 is past the end date of 7/1/2023.

I am also trying to do the same thing above but when the cells are in a quarterly date format (i.e. "1Q23"). Not sure if it is possible to do this with this format.

Thank you.Timing Model.PNG

[Gregb11]

It's always best to attach a sample workbook, that way people here don't have to recreate/type in your data. Please read the yellow banner at the top of the page.

Also, are we to assume that a quarter starts on Jan 1, then Apr 1, etc.?
If you have a start date of 2/1/23 and in cell E2 you have 4/30/23, is that 1 qtr (3 months) or 0 because it doesn't hold Q1 or Q2 fully?
I would have expected G3 to be 2, but you say you want it to be 0. If cell F2 were 7/1/23, should F3 then say 0 since F2 is passed the End date.

[StanLeakup]

Excel model date question.xlsx

Apologies, see attached Excel spreadsheet.

For simplicity, if we could assume that all quarters start on the first day of each quarter (i.e. 1/1/23, 4/1/23, 7/1/23, 10/1/23).
You are correct that if cell F2 were 7/1/23, I would like for F3 to show 0 since F2 is passed the End date. Thanks Greg.

[Gregb11]

Are the Start and End dates (values in B3 and C3) ALWAYS the start and end of a quarter, or could the Start date (in B3), be 2/1 for example?

[Gregb11]

Also, same question for dates in cells E3, F3, G3 - will these ALWAYS be the end dates of quarters, or might they be random dates (like 3/22/23)?

[Gregb11]

You can try this, but it could be wrong depending on your answers above:

in cell E3 and copy across:
=ROUNDDOWN(IF(E2>$C3,0,DATEDIF($B$3,E2+1,"M"))/3,0)

[StanLeakup]

I misspoke earlier and edited the spreadsheet. The start date in B3 will be the first day of each quarter i.e. 1/1/2023. End date in C3 will always be first day after the end of a quarter i.e. 7/1/23 or 10/1/23. Have now edited cell C3 in the post.

Cells E3, F3 and G3 will always be last day of quarter (only 3/31, 6/30, 9/30 and 12/31).

[Gregb11]

The my answer stands. Use formula in post #6 - it should work.

[StanLeakup]

Thanks Greg. How does this formula change if the End Date then becomes 4/1/2024? See attached. Thanks!

Excel model date question v2.xlsx

[Gregb11]

Sorry, I didnt know you were going to list dates down the rows. so I just needed to add the $ signs in the right place. Try this in E3, and copy across and down:

=ROUNDDOWN(IF(E$2>$C3,0,DATEDIF($B3,E$2+1,"M"))/3,0)