RaqForum 28 No.
1 Reply  •  454 View  • 
     ...

Split an Excel Table by Time Interval and Expand It Horizontally

 
split(10) excel(115)

There is a stretch of Excel data that records status and time intervals during worktime:

A

B

C

D

E

1

2

3

Name

Start

End

Activity

Elapsed hrs

4

Krishna

08:00

11:15

Production

03:15

5

Ranjith

07:00

10:10

Noload

03:10

6

Krishna

07:10

08:25

Break

01:15

7

Ranjith

07:30

12:30

Idle

05:00

8

Krishna

07:00

08:10

Production

01:10

9

Ranjith

11:55

14:55

Production

03:00

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:

G

H

I

J

K

L

M

N

O

P

07:00-08:00

08:00-09:00

Production

Idle

Noload

Tech

Break

Production

Idle

Noload

Tech

Break

1:00

1:00

1:00

0:50

0:25

0:30

1:00

1:00

0:10

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.

undefined

4. Write the following script in esProc:

A

B

C

D

1

…(Data pasted from the   clipboard）

2

=A1.import@t()

3

=create(${(["Production","Idle","Noload","Tech","Break"]*8).string()})

/Create  a table with 5*8 cols

4

for  A2

for  8

/Loop  records by 8 hours apart

5

=max(elapse@s("07:00",3600*(B4-1)),A4.Start)

/Actual  start

6

=min(elapse@s("07:00",3600*B4),A4.End)

/  Actual end

7

=if(C5

/  Actual seconds

8

=if(C7!=0,time(C7\3600,(C7%3600)\60,0))

/Seconds  to HH:mm

9

=[null]*5

/5  blank cols

10

=C9(["Production","Idle","Noload","Tech","Break"].pos(A4.Activity))=C8

/Put  HH:mm to correct position

11

=@|C9

/Append  up to 40 cols

12

=A3.record(C11)

/Insert  one record

13

=C11=null

/Reset

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.

undefined

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.


split(10) excel(115)
 
 •  454 View  •