I don't think I understood everything you are trying to do here, but I will throw some scattered thoughts at you.
1) You could use macros/VBA to do this, if you prefer VBA (or other macro language) as a programming language over spreadsheet functions. What I understood would be well within the abilities of spreadsheet formulas and utilities, so I see no real need to use VBA. Just a choice of preferred programming language. I generally prefer spreadsheet functions, so I would generally avoid VBA.
2) I technically see nothing wrong with "copy a day's worth of data into a sheet, analyze it, store the analysis results, and then overwrite with the next day's data" approach. At 50 rows per day and an Excel spreadsheet has 1E6 rows, so a single spreadsheet can hold over 50 years worth of data. When I have undertaken similar projects, I have opted for a "store all of the data in a good database in a spreadsheet, then use my spreadsheet's pivot table and function tools to summarize and analyze the data." It's up to you, but there can be value in storing all of the data together in a single spreadsheet and analyze from there rather than storing and analyzing each day separately. Pay attention to good database design, and you can summarize the data by day, by month, by quarter, by year, or whatever. Also, with a good database design, you simply append new data to the database sheet, then refresh pivot tables (and formulas if you decide to set calculation to manual).
3) I notice that your date/time values are stored as text strings. This forum is full of problems that come from storing dates/numbers as text. Your times are also stored using a 12 hour clock, but without an AM/PM indicator. If you can add an AM/PM indicator to your date/time stamps, you should be able to easily get Excel to recognize these date/time values and store them as real numbers (Text to columns would be one strategy). You will then find them easier to work with (like when you want to identify different rates at different times of the day).
4) Assuming you can store your date/times as numbers, identifying "tiers" at different times of day could easily be done using a lookup function with the approximate match option. For example, I could build a lookup table like this:
. A lookup function like =VLOOKUP(MOD(date/time_stamp,1),lookup_table,2,TRUE) would return the tier ID, and =VLOOKUP(MOD(date/time_stamp,1),lookup_table,3,TRUE) would return the rate associated with that tier. If you are unfamiliar with how the approximate match lookup option works, this could be useful:
I apologize that I have not provided specific solutions, but these are some things to consider. I would suggest that, before we start to talk specifics, we figure out how you want to resolve the dates stored as text issue.
Bookmarks