Formula for number of days passed in quarter for a given date

I work at a non profit where we need to track the length of stay according normal american quarters (Jan, April, July, October). We also need to do this for all data from past tenants. It's a small nonprofit that finds affordable housing for young adults who have aged out foster care. We are underfunded and just trying to make things work.



We have:

-one column for a move-in date.

-One column for a move-out date.



We want:

-One column to tell us how many quarters they have lived here to date, not just what quarter the move-in date falls in.

-One column to tell us how many days they have lived here in total (we have a simple date-minus-date formula here).

-One column to tell me how many days they have have been here in this quarter.



(IMPORTANT: The most important formula that I need is one that can take a move-in date and tell me how many days someone has lived here in that quarter when that quarter is finished AND "TO DATE", and similarly a formula that can do the same for a move-out date)



Thank you all so much,



B

please specify what normal american quarter is

Hi

Assume Column A Date in & column B Date Out

How many quarters lived in =CEILING((MONTH(B2))/3,1)-CEILING((MONTH(A2))/3,1)+1+INT((B2-A2)/365)*4

How many days they been in last quarter B2-DATE(YEAR(B2),LOOKUP(MONTH(B2),{1,4,7,10}),1)+1

Hi,

I have made an excel file with the formula's I think you need..
let me / us know that meets your needs or that I missed some. I got a little lost in the "footnote" where you describe the most important formula's

if anything is missing it would be a great help of you added to my file a wanted result sheet with a manualy calculated example of how you count days and quarters stayed