How to Automatically Checkmark a Column for entries that are created within a time-based timeframe
I'm trying to write a formula that will automatically place a checkmark in a checkbox column titled "Today Entry" whenever entries meet the "Created" column criteria of being submitted after 1100 AM the day prior and 0800 AM today on a rolling basis:
I've been trying to use the below column, but honestly I have no idea. I haven't been receiving error messages, just not the correct rows receiving checkmarks..
=IF(OR(AND(Created@row = TODAY() - 1, OR(AND(RIGHT(Created@row, 2) = "AM", VALUE(LEFT(REPLACE(Created@row, 1, 9, ""), FIND(":", REPLACE(Created@row, 1, 9, "")) - 1)) = 11), AND(RIGHT(Created@row, 2) = "PM", OR(VALUE(LEFT(REPLACE(Created@row, 1, 9, ""), FIND(":", REPLACE(Created@row, 1, 9, "")) - 1)) >= 1)))), (OR(AND(Created@row = TODAY(), OR(AND(RIGHT(Created@row, 2) = "AM", VALUE(LEFT(REPLACE(Created@row, 1, 9, ""), FIND(":", REPLACE(Created@row, 1, 9, "")) - 1)) = 12), AND(RIGHT(Created@row, 2) = "AM", VALUE(LEFT(REPLACE(Created@row, 1, 9, ""), FIND(":", REPLACE(Created@row, 1, 9, "")) - 1)) < 11))))), [Projected Discharge Date]@row = TODAY()), true, "")
Best Answer
-
Hi @Tobiah
Using the TIME function's format option can avoid the AM/PM format problem of the Create column.
format—[optional] Specifies whether the time is displayed using a 12-hour (0) or 24-hour (1) clock.
Then, you can use the LEFT function to extract the hour value in 24 format.
After that, you can create the IF statement comparing the date and hour values with your criteria.
[Date] =DATE(2024, 11, VALUE(MID(Timestamp@row, 4, 2)))
[Time in 24 format ] =TIME(RIGHT(Timestamp@row, 8), 1)
[From (Yesterday)] =IF(AND(Date@row = TODAY(-1), VALUE(LEFT([Time in 24 format ]@row, 2)) > [From (Yesterday)]#), 1)
[To (Today)] =IF(AND(Date@row = TODAY(), VALUE(LEFT([Time in 24 format ]@row, 2)) < [To (Today)]#), 1)
[Today Entry] =IF(OR([From (Yesterday)]@row, [To (Today)]@row), 1)You can access the demo sheet from the link below and test the formula by editing the editable Sheet Summary fields.
(Link to an editable published sheet)
Answers
-
Hi Tobiah,
Thank you for sharing the details of the issue. It seems the formula logic is quite complex. To resolve this issue, let's break it down step by step and rewrite a simpler formula.
Steps to Define the Logic
- Target Timeframe:
- From 11:00 AM yesterday to 8:00 AM today.
- Key Columns:
Created
: Determines the date and time of entry.Today Entry
: Checkbox column to indicate if an entry meets the condition.
Suggested Formula for Smartsheet
Here's a more streamlined formula to meet your requirements:
=IF(AND( Created@row >= (TODAY() - 1 + TIME(11, 0, 0)), Created@row <= (TODAY() + TIME(8, 0, 0))), 1, 0)
Explanation of the Formula
TODAY() - 1 + TIME(11, 0, 0)
:- Calculates 11:00 AM yesterday.
TODAY() + TIME(8, 0, 0)
:- Calculates 8:00 AM today.
AND()
:- Ensures the
Created
timestamp falls within the specified timeframe.
- Ensures the
IF(condition, 1, 0)
:- Marks the checkbox as checked (
1
) if the condition is true, otherwise leaves it unchecked (0
).
- Marks the checkbox as checked (
Implementation Steps
- Open the
Today Entry
column in Smartsheet. - Paste the above formula in the appropriate row or set it as a column formula (if supported).
- Test with data to ensure that rows are correctly marked based on the time criteria.
Let me know if this resolves the issue or if further refinement is needed!
Ask Me About Smartsheet Maps?!?!
Solving Automation, Integration, & Adoption Problems For Smartsheet Customers
Account Executive | Skyway Consulting Co
- Target Timeframe:
-
Hi Hunter,
Thanks for the quick response. I came across this same formula when I asked Chat GPT, however it gave me the same "#INVALID OPERATION" error message I'm receiving now:
-
Hi @Tobiah
Using the TIME function's format option can avoid the AM/PM format problem of the Create column.
format—[optional] Specifies whether the time is displayed using a 12-hour (0) or 24-hour (1) clock.
Then, you can use the LEFT function to extract the hour value in 24 format.
After that, you can create the IF statement comparing the date and hour values with your criteria.
[Date] =DATE(2024, 11, VALUE(MID(Timestamp@row, 4, 2)))
[Time in 24 format ] =TIME(RIGHT(Timestamp@row, 8), 1)
[From (Yesterday)] =IF(AND(Date@row = TODAY(-1), VALUE(LEFT([Time in 24 format ]@row, 2)) > [From (Yesterday)]#), 1)
[To (Today)] =IF(AND(Date@row = TODAY(), VALUE(LEFT([Time in 24 format ]@row, 2)) < [To (Today)]#), 1)
[Today Entry] =IF(OR([From (Yesterday)]@row, [To (Today)]@row), 1)You can access the demo sheet from the link below and test the formula by editing the editable Sheet Summary fields.
(Link to an editable published sheet)
-
Hi jmyzk_cloudsmart_jp, this is one way to work around this issue. I thought there could be a formula-based solution that avoided additional helper columns, but this does work. Thank you!
-
You just incorporate the helper columns' formula into the target column to avoid additional helper columns.
[Today Entry 2] =IF(OR(AND(Date@row = TODAY(-1), VALUE(LEFT([Time in 24 format ]@row, 2)) > [From (Yesterday)]#), AND(Date@row = TODAY(), VALUE(LEFT([Time in 24 format ]@row, 2)) < [To (Today)]#)), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!