Excel
All Top 50 Excel & Business Modeling Questions#33
Grouping Daily Transaction Timestamps into Months, Quarters, and Years
EasyPwCInterview Question #33
Asked at PwCA client transaction log has 25,000 rows with daily dates spanning 2024 to 2026 in Column A. The manager wants a quarterly summary showing revenue by Year and Quarter. Explain how to achieve this in a Pivot Table without creating helper columns for Year and Quarter in the raw data.
Input Table: DailyTransactions (A1:B4)
3 rows preview| Transaction Date (Col A) | Amount (Col B) |
|---|---|
| 2024-01-15 | 500 |
| 2024-03-22 | 800 |
| 2025-07-10 | 1200 |
Expected Output Structure2 rows
| Year | Quarter | Total Revenue |
|---|---|---|
| 2024 | Qtr1 | 1300 |
| 2025 | Qtr3 | 1200 |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Transaction Date (Col A) | Amount (Col B) | ||||||||
| 2 | 2024-01-15 | 500 | Target [Enter Formula] | |||||||
| 3 | 2024-03-22 | 800 | ||||||||
| 4 | 2025-07-10 | 1200 | ||||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
This question demonstrates a technique that can't be graded as a single cell formula (it needs another sheet, a real PivotTable, or compares two methods side-by-side). Read the explanation below instead.