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.
esProc Desktop and Excel Processing
16.1 Count date by year and month
16.3 Generate a time sequence with the same time interval – one day
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
