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.

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel