# Using COUNTIFS to try to count records between two dates

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• So, I'm trying to build a call tracker for myself to help track my calls but I'm having trouble with the COUNTIFS function. All the searches on Google were helpful and the formula looks right but it's not returning the correct value.

I have three named ranges that reference to table data. CALLCAMPAIGN, CALLDATE, and CALLDISPO (this last one isn't used yet as I'm stuck figuring out this issue).

The formula to pull by a single staticly set date works fine - =COUNTIFS(CALLCAMPAIGN,\$A5,CALLDATE,B\$1)

But I want to have two cells that contain the Start Date and End and have the formula count all the calls between those dates. The formula I've tried is

=COUNTIFS(CALLCAMPAIGN,\$A5,CALLDATE,">="&\$F\$1,CALLDATE,"<="&\$F\$2)

But instead of returning 1 for the date range I put, it is returning 0 and I can't figure out why. Any suggestions? File is attached.

## Files

• Your dates aren't formatted as dates so they cannot match.

You need a column of dates in the same format as the start and end dates. See the attached file

• Ok, that does make sense and thanks for pointing that out. I did notice that the date column that you added in looks to be just the date typed in and formatted as 'Date'.

Since the data I'm pulling is cut/pasted from the website, it's all just text values. So I was using the MID() function in the Date2 column to extract the date from Column 3. I tried tinkering with it by formatting it as 'Date' but that didn't work. I also tried adding a Date3 column formatted as 'Date' and pasting the values from Date2 into it.

So it looks like it just has to be typed in. Which will be difficult as the actual data is 1000's of records. I'll have to see if I can find a workaround for that, but thanks for the response and fix!

• That did help. And I did figure out what my issue was.

The MID() function that I was using to extract the date from the text string was storing it as TEXT even though I formatted the cells as DATE. I ended up finding the DATEVALUE() function which converts a date from text to the serial that Excel recognizes as a date. Once I did that, it started working!

Thanks for the assist!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!