How can I sum up a column based on the current time that refers to a time range and rounds down to the nearest 15 minute interval

[docmartini]

Let's say column B has the dates of a given week, column c has the time for each day broken down into 15 minute intervals and column c has the range that I want to sum up down to the nearest 15 minute interval for the current date and time.

Does anyone know how I can calculate this formula?

Any Excel Jedi warriors on here that could help me out with this? It would be greatly appreciated!

 

[FDibbins]

Not even the last one I posted yesterday at 7:15pm? I logged in as a guest on a different computer and was able to view that, so that's odd...

I think it is the browser I (am forced to) use.

This will also round down to the nearest 15 mins...
=FLOOR(A5,"0:15")

 

[docmartini]

Thanks. That is another variation that works. However the part I haven't tackled is how to sum up the range in another column down to that time range. I'm thinking I will need some type of IF function that nests in all the other formulas that I'll need...

 

[FDibbins]

You could probably do this with SUMIFS and a table containing your time slots

=SUMIFS(sum-range,time range,">="&FLOOR(A5,"0:15"),time range,<=CEILING(A5,"0:15"))

 

[docmartini]

Okay, I tried that and it says I have too many arguments.



Here's how I tried to type in the formula =SUMIFS(f:f,c:c,">="&FLOOR(now(),"0:15",C:C,<=CEILING(NOW(),"0:15"))

when I check the functions, it says that the portion shown below is invalid:
<=CEILING(NOW(),"0:15")

 

[FDibbins]

You left out the closing ) on the FLOOR function and some ""...
=SUMIFS(f:f,c:c,">="&FLOOR(now(),"0:15"),C:C,"<="&CEILING(NOW(),"0:15"))

If you are using this extensively, I would suggest that you put NOW() in it's own cell and reference it. NOW() is volatile and calcs with any workbook change - it could start to slow your file down if there are enough of them

 

[docmartini]

Hmmm, okay I fixed that, good catch. However, now it says there's an error with the formula:

=SUMIFS(f:f,c:c,">="&FLOOR(J2,"0:15"),C:C,<=CEILING(J2,"0:15"))


The 2nd critera is still coming up as invalid:

<=CEILING(J2,"0:15"))

Also, I took your advise and put the current time on a cell and referenced it.

 

[FDibbins]

you only fixed 1 of the omissions...
=SUMIFS(f:f,c:c,">="&FLOOR(J2,"0:15"),C:C,"<="&CEILING(J2,"0:15"))

If you use operators (< > =) in xxxIF() type formulas, they are seen as text and must be wrapped in "" and included using &

 

[docmartini]

=SUMIFS(F:F,C:C,">="&FLOOR(J2,"0:15"),C:C,"<="&CEILING(J2,"0:15"))

Okay, well I realized I was missing the quotes on the <= and the &, so I added them and it accepted the formula, but it came up as zero.

Odd. It doesn't seem to work.

 

[docmartini]

I revised the range just to test it out and see if it had to do with referring to an entire column and now I get a zero value.

=SUMIFS(F4:F667,C4:C667,">="&FLOOR(J2,"0:15"),C4:C667,"<="&CEILING(J2,"0:15"))

Checking on the formula, it looks like it is reading the time as a number value, where the time = 42,414 and the sum on the range is not greater than or zero to that, so it is = 0



How can I reference the time, not as a number instead? Perhaps an index function or something of that sort?

 

[FDibbins]

Time is a number.

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Sun 14 Feb 2016) is actually 42414

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

If you have something like 42,414 then that is not a real "time" value