Finding instances between time ranges that cross midnight

jbyron

New Member
Joined
Jan 13, 2020
Messages
4
Office Version
2013
Platform
Windows
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 inTime out11:00pm-11:59pm12:00am - 12:59 am1:00 am - 1:59 am2:00 am - 2:59 am3:00 am - 3:59 am4:00 am to 4:59 am
Customer A11:32 pm3:30 am111110
Customer B9:30 pm 2:30 am111100
Customer C10:45 pm12:09 am110000
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

ekrause

New Member
Joined
Aug 7, 2019
Messages
27
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,847
Office Version
365
Platform
Windows, MacOS
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

New Member
Joined
Jan 13, 2020
Messages
4
Office Version
2013
Platform
Windows
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

New Member
Joined
Jan 13, 2020
Messages
4
Office Version
2013
Platform
Windows
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

New Member
Joined
Jan 13, 2020
Messages
4
Office Version
2013
Platform
Windows
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

New Member
Joined
Aug 7, 2019
Messages
27
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,170
Messages
5,442,799
Members
405,198
Latest member
Florence Thomas

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top