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:
Calculate the minutes of each customer in each time period, as shown in the following figure:
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
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
Chinese version