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
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 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 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect