Finding instances between time ranges that cross midnight

[jbyron]

Hello all,

I feel like the answer lies here somewhere, but I'm having difficulty combining the possible answers into one, elegant solution.
I'm trying to find the number of instances that a customer comes in to when they leave. I am trying to find out how many customers are present during specific time frames (here, 11pm to 8am).

So if customer A comes in at 11 pm and leaves the next day at 3 am, and I have my headers as such, I'd like it to return the following values. Likewise, if they come in before the range of 11p-8a, but are present within the range, to still return a "1" for the fact that they are present. Any combination of logic I use gets confounded when I cross midnight. I do have the dates they came in in a column, but not the dates they left, which means I'd have to manually look for instances where it crossed midnight to fill in that is was the next day.

Thank you for any help!

	Time in	Time out	11:00pm-11:59pm	12:00am - 12:59 am	1:00 am - 1:59 am	2:00 am - 2:59 am	3:00 am - 3:59 am	4:00 am to 4:59 am
Customer A	11:32 pm	3:30 am	1	1	1	1	1	0
Customer B	9:30 pm	2:30 am	1	1	1	1	0	0
Customer C	10:45 pm	12:09 am	1	1	0	0	0	0

 

[ekrause]

Can you just do a check if Time Out is less than Time In? Something like =C2<B2.

This will only work if they are there for less than 24 hours but it doesn't seem like you have anyway to distinguish that in your current system.

Hope it helped.

 

[Jon Peltier]

Part of your issue would be solved if you enter your data as Date-Time values (13-Jan-2020 8:50 PM) rather than just times (8:50 PM).

 

[jbyron]

Can you just do a check if Time Out is less than Time In? Something like =C2<B2.

This will only work if they are there for less than 24 hours but it doesn't seem like you have anyway to distinguish that in your current system.

Hope it helped.

Thanks I tried this to identify everything that crossed midnight then used nested if statements for the rest. Mind you I'm dealing with 27,000 entries so I'm spot checking still to ensure accuracy

 

[jbyron]

Part of your issue would be solved if you enter your data as Date-Time values (13-Jan-2020 8:50 PM) rather than just times (8:50 PM).

I pulled a fresh extract and I was able to get these, how would the formula differ?

 

[jbyron]

Can you just do a check if Time Out is less than Time In? Something like =C2<B2.

This will only work if they are there for less than 24 hours but it doesn't seem like you have anyway to distinguish that in your current system.

Hope it helped.

I think I found instance where this fails, for instance if a customer comes in at 10pm, but leaves the next day at 10:59pm, without knowing that it's the next day, 10:59pm looks like it's the same day thus they are excluded from my search for people who stayed between 11pm-8am, though they did in fact stay overnight. I truly hate excel and time calculations!

 

[ekrause]

I pulled a fresh extract and I was able to get these, how would the formula differ?

You should check the dates between arriving and leaving, when they are different you would subtract the difference to determine the number of days attended. Depending on your formatting you should be able to subtract the two cells (if they have date and time in the same cell) to see the total time attended. If it comes up as a decimal adjust your cell formatting to a time format.

The formula would depend on the format of your updated cells. For instance, if the dates are included in the time value you would use a formula like this: =TEXT(B2,"d/m/yy")=TEXT(C2,"d/m/yy")

If the date values are in separate columns you should just be able to compare the columns by themselves: =B2=C2

You would obviously need to put these into an if statement for your logic calcs.

Did that answer your question?