Is there any formula to capture the current/real time in smartsheets?

sweta22

Hi there,

I want to set up an automation on a sheet where, if a form is submitted/received, within an exact 48-hour time frame I can trigger the mail. From created date column, I can get the time on when the row is created. But I'm not sure about how to accurately track the passage of 48 hours in order to send the email notification.

Is there any way to capture the current/real-time, which I can then subtract the "Created On" time and the trigger the email based on that?

Can someone help. Any other ideas/workarounds would be greatly appreciated!

Thanks.

Genevieve P.

Hey @sweta22

There is a Function that can return the current date (see the TODAY Function) but not the current time.

What I would do here is compare the timestamp of the Created Date column with a Modified Date column (these include a timestamp). We can use the new TIME function to compare the times to see if it's the same, and use the DATEONLY function to see if it's been 2 days since the original row was created.

Then we can Check a Box if that's true:

=IF(AND(TIME(RIGHT(Created@row, 8), 1, 1) = TIME(RIGHT(Modified@row, 8), 1, 1), DATEONLY(Modified@row) - DATEONLY(Created@row) = 2), 1, 0)

However, the tricky thing here is to update the system Modified date column every hour.

To do this, I personally set up 12 workflows (through the working day) to change a cell value in my "Hourly Update" column at a specific time:

Here's an example of one of them:

You'll need to make sure you change the Trigger to be Date Based and select the correct time, customizing the cadence to be Daily. I also put in a condition to check the Created Date so it only updates rows that are within the 2 day timeframe, but you don't necessarily need to do this.

Then I believe you should be able to make your trigger for the alert if the checkbox changes to checked!

Let me know if this makes sense and will work for you.

Cheers,

Genevieve