SUM between Sheet 1 & 2 for Time range

Jas16

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

Shaine Greenwood

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.