The Issue: The Time column in my table is a text value and not a time value. I need to convert it to a Time value within a countifs() function. Something like:
=COUNTIFS(TIMEVALUE(Table[Time]),">7:00:00 AM",TIMEVALUE(Table[Time]),"<=8:00:00 AM")
Obviously I cannot apply a TIMEVALUE function to an array, but I want to. How do I accomplish this? Full scenario below...
Summary: I am tracking call volumes and am using SharePoint 2016 to store my values. I have an Excel spreadsheet that is linked to the SharePoint, and another one within that same spreadsheet that converts the values. The table has the following column values:
And a few more. Each entry in this table is a call summary. For example, The date is the date of the call, the time is the time that the call happened, etc. I use the countifs() function to count frequency of calls, departments, agents, and the 3 of them together (intermixed). Here's the main problem with it: the time values I get from SharePoint come in as text values and not time; Therefore, when I try to compare it with another time value, it compares the string values of both rather than the time.
Here's what I tried so far: I know the simplest solution would be to convert each time value and place both into a DateTime column in the SharePoint itself (which is a future goal), but I need a temporary solution until I can sift through 2000 records. I am currently using a helper table to convert the time values, but it requires that I populate it with all the values, and this seems redundant so I'm trying to get rid of it.
I placed my question above for quick reference, but my question remains: how do I apply a TIMEVALUE function to an array within a countifs function? If this is not possible, can anyone offer an alternate solution? I want to remain macro free, so preferably no macros or VBA, but if it's the only solution then I will consider it.
For reference, I am using Excel 2016 and SharePoint 2016 on Office 365. The time values I want to convert to are in [hh:mm AM/PM] format.
COUNTIFS function can only handle ranges, not arrays, so you can't use any functions or operations within COUNTIFS (not to modify the ranges, anyway)
You should be able to use a mathematical operation like +0 to convert the text values to real times within a SUMPRODUCT formula, as long as there are no unconvertible text values in the range, e.g.
=SUMPRODUCT((Table[Time]+0>="7:00"+0)*(Table[Time]+0<"8:00"+0))
or if you want to count for a single hour you could use HOUR function like this:
=SUMPRODUCT((HOUR(Table[Time])=7)+0)
You can add more conditions to either of these but it'll be slower than using COUNTIFS