COUNTIFS - Time Issue

**kjcdude** · 01-23-2013, 10:24 PM

See screenshot here (http://i.imgur.com/Ck6oBuZ.png)

I'm trying to calculate the amount of arrivals that occurred between 10am and 10:59pm.
To calculate this i'm using countifs(arrivaltimerange, >=10am, arrivaltimerange, <11am)

The issue I ran into is the times are taken from the Arrival Date Time column. I use MOD(Arrivaldatetimecolumn, 1) which outputs just the time into the Arrival Time column.
For some odd reason the countifs function will not count 10:00am's. But if I type in 10:00am into the exact same cell as the one outputting 10:00 am through MOD, it will then see the 10AM and add it to the count.

It's very very bizarre since it ONLY happens on the ">=time" times and is directly related to MOD.

Any help would be appreciated.

Thanks

-- Edit
Even MORE bizarre.
If I do the countifs search for 10:30am it will correctly produce the result of 3.

**MarvinP** · 01-23-2013, 10:40 PM

Hi kjcdude and welcome to the forum,

Why not try using a Pivot Table where you group the arrival time by the hour. No formulas needed. See the attached for an example.

**kjcdude** · 01-24-2013, 06:41 PM

I didn't even think of using Pivot Tables. Definitely making a lot easier to work with the data.

I'm still confused on how you were able to define your row labels as a specific period of time.
When I crate the same pivot table I get a row label for each value, not hours. http://i.imgur.com/Gu4ruJh.png

Thanks

Originally Posted by MarvinP

Hi kjcdude and welcome to the forum,

Why not try using a Pivot Table where you group the arrival time by the hour. No formulas needed. See the attached for an example.

**MarvinP** · 01-24-2013, 06:48 PM

Right Click on any of the times and then on the sub menu word of Group. Then click on hour and make sure day isn't selected too.

See a picture of what I mean at http://chandoo.org/wp/2009/11/17/gro...-pivot-tables/ , but they don't do a group by hour.