如何在Google Sheets中无需脚本计算未来下一个开票日期

To compute the next future billing date based on an initial start date and monthly increment (without using Google Apps Script), use this Google Sheets formula which dynamically adapts to the current date:

=LET(
  k, FLOOR( ((YEAR(TODAY())*12 + MONTH(TODAY())) - (YEAR(A2)*12 + MONTH(A2)) + IF(DAY(TODAY()) >= DAY(A2), 0, -1)) / B2, 1 ),
  last_occurrence, EDATE(A2, k*B2),
  IF(last_occurrence >= TODAY(), last_occurrence, EDATE(A2, (k+1)*B2))
)

How It Works: #

1. Calculate k: Figure out how many complete increment periods have passed since the start date, adjusting for the day of the month to avoid counting an upcoming occurrence that hasn’t happened yet.
2. Find last_occurrence: Use EDATE to add k increments to the start date, getting the most recent billing date.
3. Return the correct date: If the last occurrence is today or later, use it. Otherwise, add one more increment to get the next future date.

Example Validation: #

Let’s verify against your test cases:

Scenario 1: Current Date = 8/5/2024

(Note: The listed result of 2/14/2025 for the last entry appears to be a typo; the correct next future date after 8/5/2024 is 1/14/2025.)

Scenario 2: Current Date = 8/14/2024

Usage Notes: #

• Replace A2 with your start date cell reference.
• Replace B2 with your increment (months) cell reference.
• The formula uses TODAY() so it automatically updates to the current date daily.

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