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]

Just so this is clear to us, can you share a screenshot of your data?

 

[docmartini]

Here's the image:

Not sure if that went thru on here?

I cleaned up the sheet a bit. Just to explain, I am trying to make a template when I import a csv file on the source sheet and the formula will actually be on the template sheet.

An example of what i'm trying to do is say it is 4:40 AM on 2/8/16. I want to round down to 4:30 AM and sum up the total forecasted volume. Essentially on my template I want to know what the total forecasted volume is (from column F) up to 4:30 AM (sum from F2:F19) essentially. But I need for the formula to refer to the current time and use the round down logic. I know a little bout the rounddown formula, the now formulas, if functions, etc., but I can't seem to piece this all together.

 

[docmartini]

• • Image codes: Capture

 

[docmartini]

 

[SpillerBD]

It may be more important to see your imported data.

However, you can use Running Totals in Pivot Table to get the summary you are looking for.
I believe most solutions will require will require a helper value of

=MOD(MROUND([@[Date Time]]-0.00520833333333333+1,(1/(24*60/15))),1)

 

[FDibbins]

Sorry, I cannot see any of those images

 

[docmartini]

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...

 

[docmartini]

Hi, thanks Spiller. That code looks to be a piece of the pie that I'll need, however I can't really use a pivot table in this scenario, as I need this template to work for other users who won't have the refresh the pivot table data or change the data source. They will be importing an Excel report from an external source and this document varies in size each time it is downloaded. Sometimes the document will have more rows and different time intervals to sum up the source data needed.

Not sure if I'm making sense? :/

 

[docmartini]

So I figured out how to get the current time rounded down to the nearest 15 minute interval: =ROUNDDOWN(NOW()*96,0)/96

Now I just need to sum up the volume in column F from the start of the currrent day to the nearest 15 minute interval of the current time (the variable calculated above).