Split an Excel Table by Time Interval and Expand It Horizontally
There is a stretch of Excel data that records status and time intervals during worktime:
Task: Beginning from column G, expand the data horizontally to up to 8 stretches by hour from 07:00 o’clock to 15:00 o’clock, and calculate the hours and minutes each status takes (There are altogether 5 statuses: “Production”,”Idle”,”Noload”,”Tech”,”Break”). Below are the first expected two stretches:
It’s already hard to manage to expand one row to multiple rows using Excel formula or power query. It’s more difficult in this task, which requires expanding one stretch to multiple stretches. The old ways become useless and new ways need to be sought.
1. Start esProc (Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time).
2. Open the to-be-computed file in Excel, select the area of A3:E9, and press Ctrl+C to paste it to clipboard.
3. Open esProc, select cell A1, and press Ctrl+V to paste data from the clipboard to A1. Make sure that the caret is placed above A1 in its editing status during the pasting.
4. Write the following script in esProc:
The script function elapse can get a new time after a specific time period; @s option means the time unit is second. The interval function means two time points apart; @s option means the unit of time between is second; the backslash \ and the percent sign % represent quotient and remainder respectively.
5. Press F9 to execute the SQL script. Then you can click A3 to view the result in the value view section on the right. Press Shift (for copying column headers) and click the “Copy data” button on the right-hand to paste A3’s result to clipboard.
6. Select cell G3 in Excel and press Ctrl+V to paste the result in. Finally, you can enter the time periods in the first row manually.