Countifs between 2 dates from an import range

Question

Looking for some help on a Countifs formula from an import range (if this is the right thing to use(?))

I have a sheet tracking rejected bookings on certain dates, and I want to pull this into another sheet where I am tracking total bookings, so I can find out the %.

I have a booking sheet which has the date input and the reason for rejected booking. I used an import range to pull all this data into the sheet I am working from, then tried to use a countifs between to dates to see how many for each week.

I used the following:
=countifs('Rejections'!B:B,">=24/01/2022,'Rejections'B:B,"<=30/1/2022)

I am referencing the column B containing the dates in (so they read 05/05/2022 for example).
For some reason it is just giving zeros despite there being inputs for those dates.

Any help would be appreciated!

Answer 1

There are three syntax errors in your formula: a missing exclamation mark and two missing double quotes.

The date format you show will only work when the spreadsheet locale matches it. To best avoid confusion whether 12/1 means 1st of December (month/day) or 12 January (day/month), make sure that File > Settings > Locale matches your real locale, and use the ISO8601 standard date format yyyy-mm-dd consistently everywhere.

Try this:

=countifs( Rejections!B1:B, ">= 2022-01-24", Rejections!B1:B, "<= 2022-01-30" )

See this answer for an explanation of how date and time values work in spreadsheets.