Head Count Based on In and Out Times

I am trying to get a count of people in a specific area at specific times of the day. I have 15 minute time slot columns and a check in and check out time. I'm trying to get the 15 minute time slots to enter a one(1) if the person is in the time slot between the specific in and out times. And a zero(0) if the person is not. See the example below. I realize there are 59 seconds in the start/end times. I tried doing something with a calculation of how long the person was in the area but wasn't successful.

So the first example of someone coming in at 10:39 and leaving at 11:22 has a 1 in cols E, F, G and H

Any help or thoughts are appreciated.

Thanks in advance.

Head Count To Send.yxmd

Workflow

Common Use Cases

Preparation

Expression

Accepted answers

OllieClarke

@Jeff13
If you do want the efficient solution, where the generate rows only produces 1 row per 15 minute group, I've attached it here:
The calcs for the generate rows do get a bit gnarly though...

Head Count To Send_v2.yxmd

All comments

EKasminsky

You can use the DateTimeHours and DateTimeMinutes functions. So for example, make a column called Hours and use DateTimeHours(CheckInTime) and another called Minutes with DateTimeMinutes(CheckInTime). Column 10:00- 10:14 could be if Hours = 10 and Minutes <= 14, then 1.

OllieClarke

@Jeff13 fun challenge

Here's my solution:

It does involve generating every minute between checkin and checkout, and so could be optimised, but I thought the calcs involved would get a bit complex.

Even as is, the creating of the new headers involves a few nested calcs:

Hope that helps,

Ollie

Head Count To Send.yxmd

OllieClarke

@Jeff13
If you do want the efficient solution, where the generate rows only produces 1 row per 15 minute group, I've attached it here:
The calcs for the generate rows do get a bit gnarly though...

Head Count To Send_v2.yxmd

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335