Countif and Sumproduct on date values returns zero results

%3CLINGO-SUB%20id%3D%22lingo-sub-1375913%22%20slang%3D%22en-US%22%3ECountif%20and%20Sumproduct%20on%20date%20values%20returns%20zero%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1375913%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20with%20various%20dates%20which%20are%20being%20populated%20from%20a%20database%20via%20SQL%20query%20in%20an%20excel%20sheet.%20I%20am%20trying%20to%20count%20the%20number%20of%20cells%20with%20date%2015-04-2020%20h.mm.ss.%20The%20minutes%20vary%20of%20course.%20I%20am%20trying%20to%20use%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF(IT!AF%3AAF%2C%2215-04-2020*%22)%20but%20no%20results%20are%20returned.%20When%20I%20type%20in%20any%20exact%20hour%20minute%20along%20with%20it%2C%20it%20returns%20results.%20For%20example%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF(IT!AF%3AAF%2C%2215-04-2020%203.36.29%20AM%22)%20this%20works.%20But%20the%20time%20would%20vary%20and%20hence%20I%20am%20stuck.%20I%20tried%20using%20*%2C%20%3F%2C%20~%20but%20nothing%20has%20worked%20so%20far.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20column%20AF%20has%20values%20which%20have%20the%20format%20shown%20as%20%22custom%22.%20I%20can%20temporarily%20change%20the%20format%20and%20convert%20it%20all%20to%20text%20and%20check%2C%20but%20every%20time%20the%20data%20is%20refreshed%20from%20the%20DB%2C%20it%20would%20be%20back%20to%20square%20one.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1375913%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I have a column with various dates which are being populated from a database via SQL query in an excel sheet. I am trying to count the number of cells with date 15-04-2020 h.mm.ss. The minutes vary of course. I am trying to use the following formula:

 

=COUNTIF(IT!AF:AF,"15-04-2020*") but no results are returned. When I type in any exact hour minute along with it, it returns results. For example,

 

=COUNTIF(IT!AF:AF,"15-04-2020 3.36.29 AM") this works. But the time would vary and hence I am stuck. I tried using *, ?, ~ but nothing has worked so far.

 

The column AF has values which have the format shown as "custom". I can temporarily change the format and convert it all to text and check, but every time the data is refreshed from the DB, it would be back to square one. 

0 Replies