非营利机构基于美国标准季度的租户居住时长计算需求

Hey there! Let's break down how to build those two calculation columns for your nonprofit's tenant tracking, plus the bonus of calculating days elapsed in a specified quarter. All formulas are tailored to US standard quarters starting in January, April, July, and October.

1. Cumulative Number of Quarters Resided #

This calculation needs to account for both current tenants (no move-out date) and historical tenants, and count every quarter the tenant occupied the space—even if they only stayed one day in a quarter.

Formula (Excel/Google Sheets) #

Assume:

• A2 = Move-in date
• B2 = Move-out date (blank for current tenants)

=IF(B2="",
    (YEAR(TODAY())*4 + ROUNDUP(MONTH(TODAY())/3, 0)) - (YEAR(A2)*4 + ROUNDUP(MONTH(A2)/3, 0)) + 1,
    (YEAR(B2)*4 + ROUNDUP(MONTH(B2)/3, 0)) - (YEAR(A2)*4 + ROUNDUP(MONTH(A2)/3, 0)) + 1
)

How It Works #

• Quarter Numbering: We convert each date to a unique "quarter code" by multiplying the year by 4 and adding the quarter number (1 for Jan-Mar, 2 for Apr-Jun, etc.). ROUNDUP(MONTH(date)/3,0) gives us the correct quarter number for any date.
• Current Tenants: Uses TODAY() to get the current quarter code, subtracts the move-in quarter code, then adds 1 (to include both the starting and ending quarters).
• Historical Tenants: Uses the move-out date's quarter code instead of today's, following the same logic.

Example: If a tenant moved in on 2023-11-15 (2023Q4, code = 20234+4=8096) and moved out on 2024-05-20 (2024Q2, code=20244+2=8098), the formula returns 8098-8096+1=3 quarters (2023Q4, 2024Q1, 2024Q2).

2. Total Days Resided #

You mentioned you already have a basic date subtraction, but here's a robust version that handles blank move-out dates for current tenants:

Formula (Excel/Google Sheets) #

=IF(B2="", TODAY()-A2, B2-A2)

How It Works #

• Current Tenants: Calculates days from move-in date to today.
• Historical Tenants: Subtracts move-in date from move-out date directly.

Bonus: Days Elapsed in a Specified Quarter #

To calculate how many days have passed in the quarter that a given date falls into:

Formula (Excel/Google Sheets) #

Assume D2 = The specified date

=D2 - EOMONTH(DATE(YEAR(D2), CHOOSE(ROUNDUP(MONTH(D2)/3,0), 1,4,7,10), 1), -1)

How It Works #

• DATE(YEAR(D2), CHOOSE(...), 1) finds the first day of the quarter for the specified date.
• EOMONTH(..., -1) gets the last day of the previous quarter.
• Subtracting these gives the number of days elapsed in the current quarter (e.g., 2024-02-10 returns 41 days: 31 days in Jan +10 days in Feb).

内容的提问来源于stack exchange，提问作者Bmoreno