Forum Discussion
Solved
Countif formula using date from specified cell - but partial match only
I have a set of bookings in one tab (2022 data), with their registration date in column I. I want to use COUNTIF formula to count how many bookings we've received on each date, on another tab, so th...
I had assumed that the cells contained 'real' date/time values. However, Excel does not recognize 2022-03-03T15:26:24.000Z as a date; it treats it as text. Try
=COUNTIF('2022 Data'!I1:I5000,TEXT(Z2,"yyyy-mm-dd")&"*")
Use COUNTIFS to count the number of cells with a value greater than or equal to that of Z2, and less than Z2+1.
=COUNTIFS('2022 Data'!I1:I5000,">="&Z2,'2022 Data'!I1:I5000,"<"&Z2+1)
- Thanks for the reply. Sorry, but that hasn't counted any results, and there should be one.
In case you need more info, as mentioned, in column I on the Data tab the cell includes the time too (ie 2022-03-03T15:26:24.000Z) so I've made column Z display the date in that format (yyyy-mm-dd) so I can use that to identify cells in column I that match the date. But they won't match entirely.
I've got to count several months of booking dates, so was hoping for a formula, but I can just edit the date in each line... Here I'd put in an asterisk so that it would count the date even though in Col I it's followed by other characters.
ie =COUNTIF('2022 Data'!I1:I5000,"2022-03-04*")I had assumed that the cells contained 'real' date/time values. However, Excel does not recognize 2022-03-03T15:26:24.000Z as a date; it treats it as text. Try
=COUNTIF('2022 Data'!I1:I5000,TEXT(Z2,"yyyy-mm-dd")&"*")
- Ah I see. Thanks! It works!! 🙂
