Need help - adding less than or equal value to a current formula

Justas

Hello,

Currently I'm struggling to add <= value to my current formula code, my idea is to count for Order Numbers (Unique values) and Lines of Order Numbers of certain times.

since filter doesn't work in this code, I'd like to add a <= or >= value formula for Ship_Time, so my code filters can automatically generate statistics for certain times I need.

For Orders I am using this formula:

=COUNT(DISTINCT(COLLECT({New Sheet Range 2}, {New Sheet Range 1}, ="SZE")))

here I want to add for example ">=09:00:00"

For Lines formula:

=COUNTIF({New Sheet Range 1}, [Delivered By]@row)

looking for suggestions and help!

Thank you.

Paul Newcome

Time is currently stored as a text value in Smartsheet and cannot be compared as less than or greater than. You will need to use a helper column to convert it into a numerical value and then reference that column.

There should be a solution somewhere in this thread. It is currently up to 13 pages long, but I am fairly certain there should be something in there to help get you started.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Paul Newcome

Time is currently stored as a text value in Smartsheet and cannot be compared as less than or greater than. You will need to use a helper column to convert it into a numerical value and then reference that column.

There should be a solution somewhere in this thread. It is currently up to 13 pages long, but I am fairly certain there should be something in there to help get you started.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Jeff Reisman

@Justas

To add to Paul's answer - once you have your numerical values set for time, you can simply add the remote column range for your time column and the ">=" criteria to the COLLECT function. COLLECT allows you to specify multiple pairs of criteria range and criteria.

Syntax: COLLECT( range, criterion_range1, criterion1, criterion_range2, ​criterion2)

=COUNT(DISTINCT(COLLECT({New Sheet Range 2}, {New Sheet Range 1}, ="SZE", {New Sheet Numerical Time Range}, >= 9)))