Hello All,
I created a tracker in Smartsheet and automated several features within it. One of those features automatically generates future reporting dates based on the required reporting frequency, such as every 30 days, every 45 days, quarterly, etc.
The issue I’m running into is that the dates start to drift by a few days over time. Since the tracker is built out for multiple years, that small difference eventually becomes pretty noticeable.
What I’m trying to accomplish is a formula or method that keeps the recurring dates exact based on the original start date and reporting frequency, rather than simply adding a set number of days to the previous occurrence
For example:
- 30-day (monthly) reporting
- 45-day reporting
- 91.25- day (quarterly) reporting
- Other recurring intervals
I know Excel has functions/formulas that can handle this more precisely, but I haven’t been able to figure out how to reproduce the same result in Smartsheet.
Has anyone built something similar or found a reliable way to prevent the dates from drifting over time?
Here is an example of my sheet and formula being used,
First report date due column
=IF(AND(NOT(ISBLANK([Initial Report Due Date]@row)), [Initial Report Due Date]@row >= DATE(2026, 1, 1), [Initial Report Due Date]@row <= DATE(2026, 3, 31)), [Initial Report Due Date]@row, "")
If the Initial Report Due Date exists and falls within the first quarter of 2026, return that date. Otherwise, leave the cell blank.
Future column formula
=IF(AND(NOT(ISBLANK([Initial Report Due Date]@row)), [Initial Report Due Date]@row >= DATE(2026, 4, 1), [Initial Report Due Date]@row <= DATE(2026, 6, 30)), [Initial Report Due Date]@row, IF(ISBLANK([Due Date Q1 FY26]@row), "", [Due Date Q1 FY26]@row + [Reporting Frequency]@row))
If there is an initial report due date in Q2, use it. Otherwise, if there is a Q1 due date, add the reporting frequency to that Q1 date to create the next due date.
Thanks,
JG
