RaqForum 19 No.
1 Reply  •  442 View  • 
     ...

Calculate time repeat interval

 

Example

In the Excel file book1.xlsx, there are records of the customer's entry and exit time of a certain bathroom on a certain day, and some of the data is shown in the following figure:

undefined

Calculate the minutes of each customer in each time period, as shown in the following figure:

undefined

Write SPL script:

A

1

=T("e:/work/book1.xlsx")

2

=time("18:00","HH:mm")

3

=A1.run(4.run(t1=elapse@s(A2,(~-1)*3600),t2=elapse@s(A2,~*3600),t=interval@s(max(time(A1.Enter),t1),min(time(A1.Leave),t2))\60,if(t>0,A1.~.field(#+3,t))))

4

=T("e:/work/book2.xlsx",A1)

A1 Read in book1.xlsx file data

 

A2 Start time 18:00

 

A3 Loop through each row and four time periods of A1, calculate the start time t1 and end time t2 of this period. Calculate the larger value of the customer's entry time with t1, the smaller value of the departure time with t2, and the difference t (minutes) between the two values. If t is greater than 0, save it in the (#+3)th field of the current row of A1, and # is the time period serial number.

 

A4 Save A1 to the file book2.xlsx


 
 •  442 View  •