I have a daily spreadsheet of transactions. I want to use a pivot table (or any tool you suggest) to get the sum of the transactions for specific periods of time. The period of time is 3 PM - 3 PM. So for example, I need to know the total sum for transactions from 11/26 at 3 PM to 11/27 at 3 PM, meaning the grouping is part of 2 days. How can I achieve this in this easiest way?
| Date & Time | Transaction total |
|---|---|
| 2023-11-27 17:25:07 | $8.00 |
| 2023-11-27 09:29:02 | $5.00 |
| 2023-11-26 20:23:06 | $9.00 |
| 2023-11-26 16:20:02 | $3.00 |
| 2023-11-26 10:29:01 | $10.00 |
With the example above: Group 1 would just be the bottom transaction, so the total would display $10.00. Group 2 would be the middle 3 transactions because it is between 11/26 at 3 PM to 11/27 at 3 PM, so the total would display $17.00. Group 3 would be just the top transaction because it is after 3 PM, so the total would be $8.00.
The easiest way would be to modify your data.
Your recorded time minus 15 hours would give you a time within the same 24-hour window that a pivot table could work with. So for example, 2023-11-27 3:00 PM minus 15 hours becomes 2023-11-27 12:00 AM. Then, the pivot table "Group" feature by Day would give you the desired periods, listed by start date for that period. So the above 2023-11-27 3:00 PM period start date would be 2023-11-27. Everything up to 2023-11-28 02:59 PM would also map to 2023-11-27.
So let's say your first date is in A2, your table and formulas would look like this:
| Date & Time | Transaction total | Time To Subtract | Period Start Date |
|---|---|---|---|
| 2023-11-27 17:25:07 | $8.00 | 15:00 |
=A2 - C2 |
| 2023-11-27 09:29:02 | $5.00 | 15:00 |
=A3 - C3 |
| 2023-11-26 20:23:06 | $9.00 | 15:00 |
=A4 - C4 |
| 2023-11-26 16:20:02 | $3.00 | 15:00 |
=A5 - C5 |
| 2023-11-26 10:29:01 | $10.00 | 15:00 |
=A6 - C6 |
When you create a Pivot Table off of this data, rather than using your Date & Time column, select your Period Start Date. Right-click on the first pivot table data row and select Group... Change the grouping to Day and it should give you the desired output.