Learn how to calculate current and longest streaks from dates in Google Sheets and Excel—even with gaps—using a single dynamic formula.
How do you calculate current and longest streaks from dates when your data is just a list with gaps?
Most streak formulas online solve a different problem.
They’re built for win/loss or TRUE/FALSE data—where every row clearly continues or breaks the sequence.
But that’s not what you’re dealing with here.
You’re working with dates.
And dates don’t tell you when a streak is broken—they only show when something happened.
Miss a couple of days, and your streak is already gone—but your data doesn’t say it explicitly—all you see is a list of dates with invisible gaps in between.
That’s exactly why most formulas fail.
Free Streak Tracker Template (Skip the Setup)
If you don’t want to build this from scratch, I’ve created a ready-to-use template:
Copy the Streak Tracker Template
- Tracks current and longest streaks from dates automatically
- Handles duplicate dates
- Works in both Google Sheets and Excel (Microsoft 365)
This file will open in Google Sheets. Once opened, go to File → Download to get an Excel copy.
In Excel, the formula may not work immediately. If that happens, copy the formula and paste it into a new cell. Then delete any existing error results by selecting those cells and pressing the Delete key.
Why Streak Calculations Break with Dates
Calculating current and longest streaks from dates is really about detecting continuity in time.
That means:
- Each date must be exactly one day after the previous one
- Any gap (more than one day) breaks the streak
Unlike win/loss data, missing days are not explicitly recorded.
So the real challenge is:
How do you detect breaks in a sequence of dates—and calculate both current and longest streaks—without helper columns?
Example: Streaks from Dates with Missing Days
Consider this dataset:
2026-04-01
2026-04-02
2026-04-03
2026-04-04
2026-04-05
2026-04-06
2026-04-07
2026-04-08
2026-04-09
2026-04-12
2026-04-13
2026-04-14
At first glance, it looks like a continuous sequence.
But there’s a hidden break:
April 10 & 11 are missing
So the correct result is:
- Longest streak → 9 days (Apr 1–9)
- Current streak → 3 days (Apr 12–14)
This is where most formulas return incorrect results.
Formula to Calculate Current and Longest Streaks from Dates
Use this formula in Google Sheets or Excel (Microsoft 365):
=LET(
udt, TOCOL(A2:A13, 1),
streak, SCAN(0, SEQUENCE(COUNT(udt)), LAMBDA(acc, val,
LET(
prv, IFERROR(CHOOSEROWS(udt, val-1),0),
cur, CHOOSEROWS(udt, val),
IF(cur-prv>1, 0, acc+1)
)
)),
cs, (CHOOSEROWS(streak, -1)+1),
ls, MAX(streak)+1,
IFERROR(VSTACK(cs, ls), 0)
)This formula works without helper columns and updates automatically as you add new dates.
Output: Current and Longest Streaks from Dates
This formula returns:
- First value → Current streak from dates
- Second value → Longest streak from dates
No helper columns. No manual steps.
Customize the Streak Formula for Your Data (Sheets & Excel)
The formula returns both the current and longest streaks in one go. You can easily customize the input range and output format.
Use a Larger Range
Replace:
A2:A13With:
A2:A1000Or in Google Sheets:
A2:AShow Results Horizontally
Replace:
VSTACK(cs, ls)With:
HSTACK(cs, ls)Return Only One Result
Replace:
VSTACK(cs, ls)With:
- Only current streak →
cs - Only longest streak →
ls
How This Streak Formula Works
At a high level, the formula tracks continuity between dates:
TOCOL(...)→ removes blanks and normalizes the dataSEQUENCE(...)→ generates row positionsSCAN(...)→ evaluates each date step-by-step
Inside SCAN, the key logic is:
IF(cur - prv > 1, 0, acc + 1)
- If the gap between the current and previous date is greater than 1, the streak is broken → reset to
0 - Otherwise, the dates are consecutive → increment the accumulator
Finally:
MAX(streak) + 1→ returns the longest streaklast value of streak + 1→ returns the current streak
(+1 is added because the count starts from 0)
This running comparison is what makes the formula both accurate and dynamic.
Real-World Use Cases
This method works anywhere you track activity using dates:
- Fitness and workout tracking
- Reading habits
- Meditation streaks
- Writing consistency
- Coding practice
If your dataset contains dates, you can calculate streaks reliably.
For a practical example, check out my “Free Google Sheets Fitness Tracker (Template + Dashboard)“. In that template, the streak is also reset based on an additional condition (e.g., rest days), and UNIQUE is used to remove duplicate dates caused by multiple entries in a single day.
Pro Tips for Accurate Streak Tracking
Here are a few tips to ensure your streak calculations are accurate:
✔ Remove duplicate dates
If your data includes multiple entries per day, use:
TOCOL(UNIQUE(A2:A1000), 1)✔ Combine multiple columns
You can use:
A2:B1000TOCOL will merge them into a single list.
✔ Ensure proper date format
- Use real date values (not text)
- Avoid inconsistent formats
- Clean your dataset before applying the formula
FAQ: Streaks from Dates
How do you calculate streaks from dates in Google Sheets?
You compare each date with the previous one and count consecutive days without gaps. The formula above automates this using dynamic array functions.
Can Excel calculate current and longest streaks?
Yes. Excel (Microsoft 365) supports functions like LET, SCAN, and TOCOL, making it possible without helper columns.
Why do streak formulas fail with date-based data?
Most formulas assume continuous data (like TRUE/FALSE). Date-based data contains hidden gaps, which break streak logic unless handled properly.
How do you handle missing dates in streak calculations?
By detecting gaps between consecutive dates. If the difference is greater than 1, the streak resets.
Final Thoughts
Tracking current and longest streaks from dates isn’t just a formula problem.
It’s a pattern recognition problem.
Once you understand that streaks depend on continuity and gaps, you can apply this logic to almost any dataset.
Instead of relying on rigid formulas, think in terms of:
- sequences
- gaps
- continuity
That’s what makes your solution flexible—and powerful.
