Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

SUM between Sheet 1 & 2 for Time range

Hi everyone,

I'm stuck and need a little help.

I'm trying to group Time ranges either both columns "A & B" if doable, or just column "A" into multiple 3-Hours bucket for the day with =FLOOR or similar

and pick the highest number of Customers from same bucket/ time window of 3-hours with =LARGE from Sheet 1 & Sheet 2

then sum the two highest for that 3-Hours bucket 

the above 3 steps can be done in any order as long the result is Sheet1 Bucket1 + Sheet2 Bucket1 and so on

Timestamp with number of customers similar to these following:

Sheet 1        

From / To / # Customer

A1: 7:00 AM   B1: 8:00 AM (2 customers)

A2: 8:05 AM    B2: 9:25 AM (2 customers)

A3: 9:30 AM    B3: 10:15 AM (3 customers)

A4: 10:20 AM   B4: 12:30 PM (4 customers)

A5: 12:35 PM    B5: 3:00 PM (4 customers)

        

Sheet 2        

A1: 7:00 AM   B:1 9:15 AM (2 customers)

A2: 9:20 AM   B2: 10:55 AM (2 customers)

A3: 11:00 AM   B3: 2:00 PM (3 customers)

A4: 2:05 PM   B4: 3:00 PM (2 customers)

Any or suggestion would much appreciated. I've tried a few different formulas and combinations but none works as desired

Thanks

Comments

  • Hello,

    Currently, you'd need to have this data all on one sheet or cell link the data from one sheet to another. (See our help article for more on cell linking.) 

    We don't yet have a way to work with time values, so these would have to be decimal values, such as 15.00 for 3:00 PM.

    Although we don't have FLOOR, we do have an INT function, which doesn't round, but takes only the integer portion of a number: INT(1.6) would return 1, INT(-5.3) returns -5. Details on this function here: https://help.smartsheet.com/function/int

    We are planning on implementing a LARGE function, TIME function (that lets you create formatted time of day and perform calculations on time), as well as cross-sheet formulas in Q4 of this year.

    We currently don't have plans to release an equivalent to FLOOR. When you have a moment, please submit a Product Enhancement Request using the form under Quick links on the right of the community site. 

     

This discussion has been closed.