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, "")

Find more posts tagged with

Accepted answers

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=b021415576674774bf1f16f5ca040240 (Link to an editable published sheet)

All comments

Hunter Taylor

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.
IF(condition, 1, 0):
- Marks the checkbox as checked (1) if the condition is true, otherwise leaves it unchecked (0).

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!

Tobiah

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:

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=b021415576674774bf1f16f5ca040240 (Link to an editable published sheet)

Tobiah

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!

jmyzk_cloudsmart_jp

@Tobiah

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.