Date & Time
Count unique dates while ignoring timestamps (INT + UNIQUE)
Strip fractional clock times from audit timestamps with INT before counting distinct active calendar days with UNIQUE.
Why timestamps inflate UNIQUE date counts
Spreadsheet timestamps store calendar dates in the integer part and clock time in the decimal fraction: 2026-04-01 09:00 is 46113.375, while 2026-04-01 18:00 is 46113.75.
Applying UNIQUE directly to raw timestamps treats three logins on April 1 as three distinct values! Stripping the decimal fraction with INT(Timestamp) normalizes every event on April 1 to serial 46113 so ROWS(UNIQUE(DateOnlyRange)) counts exact active days.
| Event Timestamp (A) | Serial Value | DateOnly = INT(A) (B) |
|---|---|---|
| 2026-04-01 09:00 | 46113.375 | 46113 (Day 1) |
| 2026-04-01 15:00 | 46113.625 | 46113 (same Day 1) |
| 2026-04-02 12:00 | 46114.500 | 46114 (Day 2) |
| 2026-04-01 21:00 | 46113.875 | 46113 (same Day 1 -> 2 unique days) |
Distinct active calendar days vs distinct timestamps
Normalize timestamps with INT in a clean date column so both UNIQUE and SUMIFS/COUNTIFS group events by whole calendar day.
=ROWS(UNIQUE(B2:B5))Contrast raw timestamp count without INT
Without INT, four distinct clock times return 4 instead of 2 active calendar days.
=ROWS(UNIQUE(A2:A5))Inputs and interpretation
This bounded example requires complete numeric timestamps. Blank inputs passed through INT can become zero and introduce a false day; filter missing records before applying the pattern to a larger log. Normalize time zones before stripping times. Serial values shown use the 1900 date system.
Try the example
Use the original values or change them to test your understanding.