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.