Automatically Check a Box upon entering a date and time

ahsan_mazhar101

Hello,

In my Smartsheet sheet, I'd like to set up a system to check off meetings automatically. I'd like to enter a date (ex: 04/15/23) in a column and the time span in another column (ex: 12:30 - 2:30 PM). It would then automatically check a box in next column if the start time have already passed.

Any advice would be deeply appreciated.

Thank you!

Scott Peters

Hello @ahsan_mazhar101 - To my knowledge there is not a way to do this at this time, because Smartsheet does not offer a 'Time' column format. For this to automatically update you would need a formula such as =Now() for the system to reference the current time (similar to the =Today() date function). If it helps, there are some great formulas out there to convert times to usable numbers, such as this:

Formulas for Calculating Time

Paul Newcome

Jun 8, 2020

I have decided to create a post that consolidates all of the different time based solutions that I have put together.

Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.

I thought it would be helpful to have them all in one place instead of spread out.

I welcome all feedback, suggestions, and additions.

I'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.

I will also try to post various tips and tricks as I think of them as well such as:

It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))

Calculating Time Worked for Employees

Can you calculate time in Smartsheet?

Conversion of timezones (Solved Formula Included)

Date/Time/Year

Flagging a Date and Time Overlap

How do I create time of day columns?

Need to create a "shift" column from a time column

---

View Post

I hope this helps