"There is a stretch of Excel data that records status and time intervals during worktime: A B C D .."

blackduckie RaqForum 28 No.
1 Reply • 353 View • 3 Years ago

Split an Excel Table by Time Interval and Expand It Horizontally

split(10) excel(94)

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
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