CountIf NetWorkDays Formula Results

**ClikClak** · 03-03-2010, 04:04 PM

I have a formula that calculates the amount of hhmmss between 2 date times using the networkdays function. Is there a way to use a countif formula to count the number that are:

< 00:59:59
> 01:00:00 and < 01:59:59
> 02:00:00 and < 02:59:59
> 03:00:00

each one of the 4 lines would be a seperate formula.

Here is what I have but it isn't working

=COUNTIF(D:D,"< 00:59:59")

**NBVC** · 03-03-2010, 04:15 PM

Do the cells in column D have dates and times or just times?

**zbor** · 03-03-2010, 04:17 PM

With XL2007 you can use COUNTIFS:

=COUNTIFS($H$1:$H$20,">=1:00:00",$H$1:$H$20,"<2:00:00")

or this in one row and pull formula down (so you don't need to change numbers manualy)

=COUNTIFS($H$1:$H$19,">="&2.5*ROW(A1)/60,$H$1:$H$19,"<"&2.5*(ROW(A1)+1)/60)

**6StringJazzer** · 03-03-2010, 04:25 PM

You just need to remove the space:

=COUNTIF(D:D,"<00:59:59")

As shown above, you can include multiple criteria using COUNTIFS in 2007.

Also, I'm not sure why you are using NETWORKDAYS if you just want the difference between two times. NETWORKDAYS returns an integer number of weekdays between two dates, not times as you are using in your comparison. To get the time between two times, just subtract.

**ClikClak** · 03-03-2010, 04:45 PM

@NBVC

The cells in column D have just times like this: 00:47:34

**NBVC** · 03-03-2010, 04:50 PM

Have you tried zbor's suggestions...

you can also use

=COUNTIFS($H$1:$H$20,">"&Time(7,0,0),$H$1:$H$20,"<"&Time(14:00))

to get times between 7 AM and 7 PM, for example

You can replace the TIME() function with cell references to containing the upper and lower time limits...

**ClikClak** · 03-03-2010, 05:01 PM

Yes, I used zbor's for the ranges and 6StringJazzer was right on my formula, I had an extra space. It is working now for all 4 ranges

Thanks to everyone