The Complete Quarter Start Formula
Breaking it down piece by piece
The Complete Formula
Full formula:
D - DAY(D) + 1
- (MIN(MOD(MONTH(D) - 1, 3), 1) × DAY(D - DAY(D)))
- (MAX(MOD(MONTH(D) - 1, 3) - 1, 0) ×
DAY(D - DAY(D) - DAY(D - DAY(D))))
We'll break this into four components:
- ⓵ Anchor: D - DAY(D) + 1
- ⓶ Position: MOD(MONTH(D) - 1, 3)
- ⓷ MIN Switch: MIN(position, 1) × previous month's days
- ⓸ MAX Switch: MAX(position - 1, 0) × two months back days
Why we need this approach
The Problem:
Glide Math doesn't support IF/THEN conditional statements
The Solution:
Use multiplication with 0 (off) and 1 (on) as switches
0 × days = 0 (don't subtract anything)
1 × days = days (subtract all the days)
⓵ Component 1: Anchor to the 1st of this month
Formula:
D - DAY(D) + 1
What it does:
Gets you to day 1 of the current month by subtracting the day number
Example (August 15, 2024):
Aug 15 - 15 + 1
= August 1, 2024
Why important:
We need an anchor point. Then we subtract backwards to reach the quarter start
⓶ Component 2: Find position in quarter cycle
Formula:
MOD(MONTH(D) - 1, 3)
What it does:
Tells us where in the 3-month cycle we are: 0, 1, or 2
Returns 0 for 1st month, 1 for 2nd month, 2 for 3rd month
Example (August 15, 2024):
MONTH(Aug 15) = 8
MOD(8 - 1, 3) = MOD(7, 3) = 1
Position = 1 (2nd month of quarter)
Why this works:
Quarters repeat every 3 months, so MOD always gives 0, 1, or 2
This pattern works for all quarters, all years, forever
Visualizing the gap: Stepping back through previous months
Case 1: Position 0 - Already at quarter start
Case 2: Position 1 - Step back 1 month to reach July 1st
Case 3: Position 2 - Step back 2 months to reach July 1st
Now we handle all three positions with switches
Case 1: Position = 0 (First month of quarter)
Example: January 15, 2024
Why: Already at quarter start, nothing to subtract
MIN Switch:
MIN(0, 1) = 0
Don't subtract 1 month's days
0 × 31 days = 0
MAX Switch:
MAX(-1, 0) = 0
Don't subtract 2 months' days
0 × days = 0
Result: January 1 (no subtraction)
Both switches OFF → 0 + 0 = 0 days subtracted total
Case 2: Position = 1 (Second month of quarter)
Example: August 15, 2024
Why: Subtract 1 month to reach quarter start
MIN Switch:
MIN(1, 1) = 1 ✓
Activate
1st month back: July
July has 31 days
1 × 31 = 31 days
MAX Switch:
MAX(0, 0) = 0
Don't use
(not in 3rd month)
No 2nd month back subtraction
0 × days = 0
Result: August 1 - 31 days = July 1, 2024
MIN ON (31 days), MAX OFF (0 days) → 31 days subtracted total
Case 3: Position = 2 (Third month of quarter)
Example: September 15, 2024
Why: Subtract 2 months to reach quarter start
MIN Switch:
MIN(2, 1) = 1 ✓
Activate
1st month back: August
August has 31 days
1 × 31 = 31 days
MAX Switch:
MAX(1, 0) = 1 ✓
Activate
2nd month back: July
July has 31 days
1 × 31 = 31 days
Result: September 1 - 31 - 31 = July 1, 2024
MIN ON (31 days), MAX ON (31 days) → 62 days subtracted total
Both switches activate because we need to go back 2 months
The Magic: How DAY() calculates month lengths automatically
This is why the formula handles leap years without special cases
DAY(D - DAY(D)) → Get days in previous month
D - DAY(D) lands on last day of previous month
For Aug 15: Aug 15 - 15 = July 31 (last day of July)
DAY(July 31) = 31 (tells us July has 31 days)
Automatically works for Feb (28/29), 30-day months, etc. No special cases!
DAY(D - DAY(D) - DAY(D - DAY(D))) → Get days 2 months back
Start: Last day of previous month
Subtract all its days → Last day of month before that
For Sep 15: July 31 - 31 days = June 30 (last day of June)
DAY(June 30) = 30 (tells us June has 30 days)
Works across year boundaries and all leap years automatically!