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

Devraj

@Genevieve P. Thanks Genevieve. Can you please help me with below situation:

I have created a cutover Plan in Smartsheet and all tasks in the plan has these columns : Task, Duration, start Date and Start time, End Date and End time, Actual Start and Actual Time, Actual Finish Date and Actual Finish time.

I am able to calculate start and end dates based on duration but I’m only able to capture Actual start and Actual Finish dates based on workflow responses once the task gets triggered and submitted by the user as 100% done.

What is the way or formula to capture actual actual Finish time? Actual dates are captured using formulas but not actual end times

I want to use the Planned end time with actual end time to calculate variance and Reforcast the cutover plan end dates.

any help around the above situation will greatly benefit me. Thanks in advance

Devraj

@Genevieve P. Any help on above comment will be highly appreciated

Genevieve P.

Hi @Devraj

You can calculate the difference between dates in Days, but there isn't a direct Time Column in Smartsheet at the moment to be able to easily calculate times (other than using the formula function noted above).

I would suggest adding your vote to this Product Idea: Please let Smartsheet work with time

Paul's post with some time formulas may be helpful as well: Formulas for Calculating Time