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.

Directions:

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.

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

4 Tips to Level up Your GO Programming

Implementing HA on MSSQL Server (AlwaysOn Availability group)in an on-premise environment (Part 1)

Creating key pair, security group, launch an instance on AWS, create EBS volume and attach this…

Solution: Roman to Integer (Python)

YouTrack Workflows — how do we customize our project managing tool (part II)

He Reads Code like Books

Modern computer code (https://www.google.com/url?sa=i&rct=j&q=&esrc=s&source=images&cd=&cad=rja&uact=8&ved=2ahUKEwix5bKN_67lA

CS 373 Spring 2022: Carlos Vela (Week of 21 Feb. — 27 Feb.)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
chuxin huo

chuxin huo

hello:)

More from Medium

Betterspace

CS373 Spring 2022 Ricky Woodruff — Blog 7

I don’t like how parkour style competitions are judged

“What Is CFA? full form,Course Details, Syllabus, Exam fees,Duration,eligibility

https://mentormecareers.com/what-is-cfa/