16.2 Calculate time repeat interval
There is a registration table, which records the entering and leaving time of customers in a certain bathroom on a certain day:
The task is to calculate the number of minutes each customer stays in each time period, as shown in the following figure:
Enter in cell D2:
| A | |
|---|---|
| 1 | =‘D$1’.split(“-”).(interval@s(time(“00:00”,“HH:mm”),time(~,“HH:mm”))/60) |
| 2 | =min(‘$C2’*1440,A1(2))-max(‘$B2’*1440,A1(1)) |
| 3 | =if(A2>0,A2,null) |
A1: Split D1 into two values by -, and then convert them to the number of minutes from 00:00
A2: The time stored in this Excel table is obtained by dividing the seconds from 00:00 to the present by 86400, so multiplying the time by 1440 is the number of minutes from 00:00 to the present. Calculate the larger value of customer’s entering time and the start time, and the smaller value of customer’s leaving time and the end time, and then calculate the time difference (minutes) between the two values.
Then drag D2 to every relevant row and column.
https://c.scudata.com/article/1651916536524
https://c.scudata.com/article/1659317771338
https://c.scudata.com/article/1659406546685
SPL Official Website 👉 https://www.esproc.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.esproc.com
Discord 👉 https://discord.gg/2bkGwqTj
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
