Trying to figure out how to count the number of instances between several time frames...

I have attached sample date, but basically I need to sort and filter thousands of items and of the 10,000 items, I need a count of how many were between the following times and then chart the results:

1. # between Friday at 6pm until Monday at 8am
2. # before 8am on Tuesday through Friday
3. # 8am and 8:30pm Monday to Friday
4. # between 4:30pm and 6pm Monday to Friday
5. # after 6pm on Monday through Friday

I am really lost and thought I could figure it out easily but I'm guessing that the data needs to be manipulated somehow for any =if formula to work. Can anyone help? If I have to do this manually it would take me weeks!

Thank you.

Put this in E2 and copy it down for each entry.

=IF(OR(WEEKDAY(C2,2)+D2<1+"8:00 AM",WEEKDAY(C2,2)+D2>=5+"6:00 PM"),1,LOOKUP(D2*24,{0,8,16.5,18},{2,3,4,5}))

This will categorize each entry with 1 to 5 according to your criteria. Category 3 was changed to 8am and 4:30pm Monday to Friday. I assume that was a typo as it originally overlapped Category 4.

You could then use COUNTIF to count each category or create a pivot table to filter in a variety of ways.

Example: count category 1
=COUNTIF(E:E, 1)

Thank you SOOOOO much AlphaFrog! I spent hours and I mean HOURS...trying to figure out how to do this with pivot tables, if commands, filters, sorting, but what threw me off was that I had no clue how categorize all of them in one simple formula!! I can't thank you enough because without your reply I would have been trying to figure this out for awhile and then sort to counting it manually (and although the sample was pretty small), the actual file is thousands of rows! I don't really understand the formula, so if I had to change Category 3 from 8am to 8:30pm Monday to Friday, how would the formula be changed (if it is even possible).

Did I mention....you are a life saver??? Literally! Instead of wasting my life on trying to figure this out by myself for the next 10 years or so, I can actually relax and enjoy the long weekend! So...I know, I have said it already...TWICE!! But thank you soooo soooo much! Can you tell I really appreciate it? If there is some sort of survey on your assistance from 1-10...you get 100 (yes 100)

Kallie

Oops...I made a mistake in my original time frames...

#3 is supposed to be 8:00am to 8:30am Monday through Friday (during the first half hour of each work week day)
#5 is supposed to be between 6:00pm and 8:00am on each day from Monday to Friday (but stopping at Friday morning at 8:am so as not to include Friday evening to Saturday morning)

I hope this is a simple fix!

You're welcome. My motto has always been; "Saving lives one formula at a time".


IF(OR(WEEKDAY(C2,2)+D2<1+"8:00 AM",WEEKDAY(C2,2)+D2>=5+"6:00 PM"),1

This part categorizes Friday 6:00 PM to Monday 8:00 AM as cat 1



,LOOKUP(D2*24,{0,8,16.5,18},{2,3,4,5}))

This part categorizes the weekday daily time ranges.

• D2*24 converts serial time into hours (0 to 23.999) where 8 = 8:00 AM and 16.5 = 4:30 PM ...etc.
• LOOKUP looks up the hours value greater than or equal to the values in the array {0,8,16.5,18} and returns the corresponding match from {2,3,4,5}
• If you want to change the 4:30 PM category, change the 16.5
• This method doesn't allow for overlapping categories.

You again are a life saver...still a little confused though by the formula (I know simple formulas)
My supervisor can't get her times straight, as she kept telling me different times each time and I got so confused, but now have the final corrections with 6 time frames:

1. # between Friday at 8pm until Monday at 8am
2. # before 8am on Tuesday through Friday (so this is from midnight to 8am on Tuesday, Wednesday, Thursday and Friday)
3. # 8am and 8:30am Monday to Friday
4. # between 4:30pm and 6pm Monday to Friday
5. # after 6pm on Monday through Friday (so this is 6pm to midnight on Monday to Thursday night)
6. # from 6pm on Friday to 8pm on Friday

Is it difficult to do this? Hopefully this doesn't overlap the times. I'm trying to figure out the formula myself but it will take me some time to understand it properly. Thanks again...I apologize for the number of changes, just wish my boss didn't keep making mistakes on what she was asking for...mainly all typos, as one minute she said category 1 was from 6pm, then it was 8pm, then 6pm, then 8:30 pm.

Again, thank you so much!

What about 8:30 AM to 4:30 PM Monday to Friday ?

This can be done. My formula description didn't make sense?

Your formula did make sense, but I'm worried I will do something wrong and get an error like I normally do. You asked about 8:30 to 4:30, would this be a 7th category? If its not too much trouble I can't see that hurting anything. I think once I know the formula is correct then I will feel confident trying to manipulate the formula further and play around with it to understand it better. Again, thank you so much! People like you are hard to come by and are appreciated more than you could possibly know!

Originally Posted by Kallie26

You asked about 8:30 to 4:30, would this be a 7th category?

I don't know if that's a 7th category. You have to say what you want. It's your criteria.

All I know is it's a gap in the weekday time that you didn't categorize in your last criteria. So what do you want to do with it?

Please Login or Register  to view this content.

Category 1
IF(OR(WEEKDAY(C2,2)+D2<1+"8:00 AM",WEEKDAY(C2,2)+D2>=5+"8:00 PM"),1

Category 6
IF(WEEKDAY(C2,2)+D2>=5+"6:00 PM",6

Categories 2 - 5
LOOKUP(D2*24,{0,8,8.5,16.5,18},{2,3,"???",4,5})

8:30 AM to 4:30 PM Monday to Friday returns "???". Change it to suit.

Oh, I get it now. When it was separated, I didn't know how to put it all together. It totally makes sense!! I'm not sure if this is a long weekend for you or not, but either way, enjoy your weekend!

By the way, I love your motto because it is so true!

Thanks yet again!!!