excelexcel-formulapivot-table

# How can I sort a spreadsheet by time period

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.

Solution

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