Assistance with Formula for 30-Minute Meeting Reminder

Brittanyy

Hi all,

I need assistance with a formula where I want the box to check 30 minutes prior to the meeting, but I’m hitting a roadblock.

The "Start Date" is a Text Column. Below is the formula I’ve tried:

Start Date Helper Formula: =IF(ABS(NOW() - (DATE( VALUE(LEFT([Start Date]@row, 4)), VALUE(MID([Start Date]@row, 6, 2)), VALUE(MID([Start Date]@row, 9, 2))) + (VALUE(MID([Start Date]@row, 12, 2)) / 24) + (VALUE(MID([Start Date]@row, 15, 2)) / 1440) - (30 / 1440))) <= (1 / 1440), 1, 0)

I have also tried this formula: =IF(ABS(NOW() - ([Start Date]@row - (30 / 1440))) < (1 / 1440), 1, 0)

and this formula: =IF(ABS(NOW() - (DATEVALUE(LEFT([Start Date]@row, 10)) + TIMEVALUE(MID([Start Date]@row, 12, 5)) - (30 / 1440))) <= (1 / 1440), 1, 0)

I’ve attached a screenshot for reference.

Any guidance would be greatly appreciated!

Thank you in advance,

Paul Newcome

There is no NOW function in Smartsheet. The best we could do (without the API or the premium add-on Bridge) is closer to an hour. Would that be acceptable?

Brittanyy

@Paul Newcome 1 hour can work. Would would I use in place of "NOW"?

Paul Newcome

You would need to set up 24 automations. I usually use change cell automations and have a dedicated column. Basically set them all up to run daily but then adjust the time so there is one running each hour of the day and putting a new letter in the dedicated column. This basically changes the Modified (date) column on an hourly basis at which point you can compare that time with the time in the start date/time column and flag accordingly.