Persistent Delay in Updating Smartsheet Leave Request Reports

Anoop

Dear All,

I am writing to bring to your attention an issue I have been experiencing with Smartsheet that pertains to the automatic updating of reports in the dashboard.

I have a leave request form in which there are three key columns: Start Date, End Date, and Leave Status. The Leave Status column utilizes the following formula:

=IF(AND(TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row), "On leave today", IF([Start Date]@row > TODAY(), "Upcoming Leave", ""))

This formula effectively helps in identifying whether a staff member is on leave today or has an upcoming leave scheduled.

I have created two separate reports published in the dashboard—one for "On Leave Today" and another for "Upcoming Leave." However, I have encountered a persistent issue where the data displayed in both reports remains static until I manually open the main sheet and save it. Even after doing so, the update only lasts for a short period, necessitating multiple manual saves throughout the day.

This inconsistency in automatic updates is affecting the real-time accuracy of the leave status reports. I would greatly appreciate your assistance in resolving this matter to ensure seamless and timely updates in the dashboard reports.

Thank you for your attention to this issue, and I look forward to your guidance on a solution.

Julie Fortney

I have a partial explanation -

The issue is the TODAY() formula only updates to the current date when the sheet is viewed or updated. So unless there is some activity on the sheet, it doesn't recognize that today is a new day.

Unless I am sure that a sheet will have activity every single day, I add a helper date column with a workflow to record the current date every day at 1am. I reference that column in place of the TODAY() function.

This doesn't explain why you have to refresh the sheet multiple times throughout the day, though.

Maybe give the above a try and post more details if the issue persists.

Anoop

@Julie Fortney, thanks but can you explain bit more on how to use the today() function and the workflow.

thanks again.

Julie Fortney

Here's an example sheet. I've added a Today Helper column:

Here's the workflow I set up to update the Today Helper date every day. I would select 1:00 AM or 12:00 AM, not 1:00 PM like I accidentally did in this gif. :)

I would also set up a second version of this workflow, only make it trigger when a row is added. (otherwise it won't populate today's date until the next time the daily workflow runs.)

Here's an updated version of the Leave Status formula with a few tweaks:

=IF([End Date]@row < [Today Helper]@row, "Past Leave", IF(AND([Start Date]@row <= [Today Helper]@row, [End Date]@row >= [Today Helper]@row), "On leave today", "Upcoming Leave"))

Let me know if you have other questions.