Basics of Spreadsheet & Formatting. Tab name = HFC
-------------B--------------------------C------------------------------- D---------------------- F-----------------------Column
1--------- Day----------------------- Date ----------------------------Time ------------------Source---------------------Row
2 |=TEXT(C2,"dddd")|----------MM/DD/YYYY HH:MM:SS|----------=TEXT(C2,"HH:MM")|---------Text-----------------------Cell Formats
3-------Saturday---------------3/1/2013 12:16:37 AM-----------------00:16-------------------Outage---------------------Data



On a separate tab the goal is to get the number of calls "Outage" generated on a specific day, during a specific hour hour.

This part of the query works:
=COUNTIFS(HFC!F:F,"Outage",HFC!B:B,A311) gets me all outages on Saturday

When I add time to the Query it returns a 0:
=COUNTIFS(HFC!F:F,"Outage",HFC!B:B,A311,HFC!D:D,"& Time>=0,0,0",HFC!D:D,"&Time<1,0,0")

I have tried replacing time values 0,0,0 & 1,0,0 with 00:00 & 01:00 with the same results.

Also tried pasting the Date/Time Data to all 3 cells and formatting each cell to display only the needed data. That countif didn't work at all.

I can't figure out where I am going wrong in the query or if there is a way to get the data to display as a number instead of text.
Appreciate any help I can get

I never even knew the COUNTIFS function existed; I sat there wondering why you were trying to use multiple conditions in a COUNTIF function, at first. So my questions may not be very worthwhile. Still, there are two things about your second COUNTIFS call that I don't understand (and I suspect that Excel doesn't either):

1) x=<1,0,0. What does this mean?
2) Why is there an ampersand at the beginning of the string?

I surmise you're trying to add a condition that the time cell must contain a positive number less than 24 hours. If so, I would have tried it this way:

=COUNTIFS(HFC!F:F,"Outage",HFC!B:B,A311,HFC!D,"> =0",HFC!D,"<1")

Could be I'm still missing something important about what can be done with these conditions.

THanks for responding & Glad that you found the countifs. Your suggestion didn't work so here are the answers to your questions:

The ampersand was added by the formula helper and the coding 1,0,0, is iin the format H,M,S.

Actually I am trying to get the number of entries on a specific day between specific housr, in this case Midnight and 1 AM.

Hm, well, I don't know what the function helper was thinking but the fact that it did that may mean there's an easier way to do this that I don't know about. (You'll remember that I've never used COUNTIFS before.) But now I see what you mean about capturing times in a certain slot, say midnight to 0100.

Let's start over. You have a full timestamp in col C. Col B displays the day of the week, and col D the time of day. You want to count up how many rows there are where col F is "Outage" and the outage occurs a) on a specified day of the week and b) within a specified hour of the day.

I now know how to go about it—I just tested it in my own spreadsheet—but first let me suggest one minor change that I think will make it simpler: Change the formula in column D from =TEXT(Cn,"hh:mm") to =Cn-INT(Cn). That strips off the days from the timestamp, leaving just the fractional part, which indicates the time of day.

Next: Rather than hard-code the day in the formulae you're using a value specified in A$311. I suggest you do the same for the time slot: You can put in 0 for midnight-to-0100, 1 for 0100-0200 and so on. Personally I would have put those in a more accessible set of cells such as G2 and G3, but since you're already using A311 I'll pretend we're going to put the hour argument in there too. So in A312 you put a number between 0 and 23. Then in A313 and A314 you put the starting and ending times that will go in the COUNTIFS formula, as follows:

A313: =">="&TIME($A312,0,0)
A314: ="<"&TIME(A$312+1,0,0)

The COUNTIFS call, then would look like this:

=COUNTIFS(HFC!$B:$B,$A$311,HFC!F:F,"Outage",HFC!D: D,A$313,HFC!D,A$314)

Couldn't you just put ">="&TIME(A312,0,0) and "<"&TIME(A312+1,0,0) directly in the formula? Sure. It might be better, too; I just liked the look of breaking up the long formula into smaller parts.

This seems to work on my PC.

Thanks for all of your help. Still no luck getting this to work. I've tried adding colums to the HFC Sheet to get the data thinking it might make it easier.

I have added a copy of the spreadsheet (with bogus data) in case you or someone else wants to play with it some more.

Resolved!

Changed the time call to =HOUR(C2)
so the query is =COUNTIFS(Time,0,HFC!B:B,A311,HFC!F:F,"Outage")
Kind of a PITA to change the query fro each day and hour but it's working. Thanks for the help.