How to Split Excel Hours Over 24 Hours into Multiple Rows
Question details
The user needs to convert monthly budgeted hours by job title into an import-ready format by splitting any durations exceeding 24 hours into multiple rows.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing monthly budgeted hours data for import into a target attendance system that limits duration values to a maximum of 24 hours per row entry.
- Observed behavior
- The source system outputs aggregate total durations (e.g., 60 hours) in a single row, which fails to import into the target system unless broken down into 24-hour daily increments and a remainder (e.g., 24, 24, 12).
Ensure your version of the spreadsheet application supports dynamic array functions, particularly LET, TOCOL, and SEQUENCE, as the primary solution relies on these advanced capabilities to unpivot and duplicate rows.
Use Dynamic Array Formulas to Unpivot and Split Hours
Apply a combination of LET, TOCOL, IFS, and SEQUENCE functions to automatically divide large durations into 24-hour chunks and unpivot the data into an import-ready table.
This approach leverages modern array calculation logic to evaluate the total hours dynamically. It determines the number of rows required by calculating ROUNDUP(hours/24, 0). Full days are assigned a value of 24, and the remaining fractional hours are assigned to the final appended row.
Select an empty cell in a new worksheet where you want the resulting unpivoted and split import data to start generating.
Enter the formula: =LET(proj, BudgetData!B1, clnt, BudgetData!B2, table, BudgetData!A4:G10, vals, DROP(table, 1, 1), test1, vals<>0, jobs, TOCOL(IFS(test1, TAKE(DROP(table, 1),, 1)), 2, 1), dates, TOCOL(IFS(test1, DROP(TAKE(table, 1),, 1)), 2, 1), hours, TOCOL(IFS(test1, vals), 2, 1), days, ROUNDUP(hours/24, 0), cols, SEQUENCE(, MAX(days)), offs, cols-1, test2, days>=cols, CHOOSE(SEQUENCE(, 6), proj, clnt, TOCOL(IFS(test2, jobs), 2) & " - " & proj, TOCOL(IFS(test2, dates+offs), 2), 1/1440, TOCOL(IFS(days=cols, hours-offs*24, days>cols, 24), 2)))
Modify ranges like BudgetData!B1, BudgetData!B2, and BudgetData!A4:G10 in the formula's declaration section to match the exact locations of your source data's project name, client name, and main data table.
Once the dynamic array resolves, you will see your original totals split across multiple rows (e.g., 60 hours into 24, 24, and 12). Copy the entire spilled array and paste it as values before exporting the sheet to a CSV file for your target system.
Split and Manage Large Data Imports Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides full, native support for advanced dynamic array functions like LET, TOCOL, and SEQUENCE, making it perfectly equipped to unpivot tables and split complex duration data without needing complex VBA macros.
- 1. Open your data file in WPS Office: Launch WPS Spreadsheet and open the workbook containing your monthly budgeted hours.
- 2. Apply the dynamic array logic: Select a blank destination sheet and paste the recommended LET formula, adjusting the source ranges to match your table.
- 3. Paste as values: Select the generated array data, copy it, and use 'Paste Special > Values' to lock the newly split row data in place.
- 4. Export to your target system: Click 'Menu > Save As' and choose CSV (Comma delimited) to create a clean import file for your attendance tracking software.
Frequently Asked Questions
Why does my attendance software reject spreadsheet durations over 24 hours?
Many attendance systems and ERPs are strictly designed to track daily shifts. They use standard date-time serialization where 24 hours constitutes an exact full day. A duration of 60 hours in a single cell line is treated as logically impossible for a 24-hour daily tracking paradigm.
What if my version of Excel doesn't support the LET or TOCOL functions?
If you are using an older spreadsheet version that lacks dynamic array capabilities, the provided formula will return a #NAME? error. In this case, you must either upgrade your software, use WPS Office which supports these arrays, or rely on Power Query and VBA macros to restructure the data.
How does the dynamic array formula calculate the remainder for the last row?
The formula subtracts the full 24-hour blocks from the initial total hours using the expression 'hours-offs*24'. It applies this logic only when the sequence iteration equals the total calculated days, outputting the exact fraction of hours left over for the final row.
Can I use Power Query to split these hours instead of formulas?
Yes. In Power Query, you can Unpivot the job columns, divide the value by 24 to find the total days, use List.Numbers to generate a sequence of rows, and conditionally assign 24 hours to full days and the modulo remainder to the last sequence item.
