Home

Countifs and how to ignore timestamp

%3CLINGO-SUB%20id%3D%22lingo-sub-401252%22%20slang%3D%22en-US%22%3ECountifs%20and%20how%20to%20ignore%20timestamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401252%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20use%20countifs%20to%20count%20a%20specific%20criteria%20on%20a%20certain%20date%20however%20in%20the%20date%20cell%20is%20also%20a%20timestamp.%20Because%20of%20this%20it%20doesn't%20count%20any%20other%20cell%20with%20this%20date%20because%20the%20time%20stamp%20is%20different.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20how%20to%20get%20rid%20of%20the%20time%20stamp%20or%20have%20the%20countifs%20not%20count%20the%20timestamp%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-401252%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401395%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20and%20how%20to%20ignore%20timestamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401395%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESeems%20so!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401336%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20and%20how%20to%20ignore%20timestamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401336%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F135331%22%20target%3D%22_blank%22%3E%40Ramon%20Haagen%3C%2FA%3E%26nbsp%3B%2C%20I%20guess%20that's%20duplication%20of%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FCountifs-and-how-to-ignore-timestamp%2Fm-p%2F401274%23M28508%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FCountifs-and-how-to-ignore-timestamp%2Fm-p%2F401274%23M28508%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401331%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20and%20how%20to%20ignore%20timestamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401331%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F135331%22%20target%3D%22_blank%22%3E%40Ramon%20Haagen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ETo%20use%20%3C%2FSPAN%3ECOUNTIFS%20you%20cannot%20change%20the%20datestamp%20as%20it%20will%20become%20an%20array%20rather%20than%20a%20range%20reference.%26nbsp%3B%20The%20way%20around%20is%20to%20test%20the%20timestamp%20against%20both%20the%20start%20and%20the%20end%20of%20the%20day.%3C%2FP%3E%3CP%3E%3D%20COUNTIFS(%20Date%2C%22%26gt%3B%3D%22%26amp%3BINT(Target)%2C%20Date%2C%22%26lt%3B%22%26amp%3BINT(Target)%2B1%20)%3C%2FP%3E%3CP%3EAn%20alternative%20is%20to%20use%20an%20array%20formula%20to%20test%20the%20date%20only%3C%2FP%3E%3CP%3E%3D%20COUNT(%20IF(%20INT(Date)%3DINT(Target)%2C%201%20)%20)%3C%2FP%3E%3CP%3EIf%20you%20need%20to%20test%20every%20date%20in%20a%20list%20against%20the%20list%2C%20this%20can%20be%20done%20using%3C%2FP%3E%3CP%3E%3D%20COUNTIFS(%20Date%2C%22%26gt%3B%3D%22%26amp%3BINT(Date)%2C%20Date%2C%22%26lt%3B%22%26amp%3BINT(Date)%2B1%20)%3C%2FP%3E%3CP%3Ewith%20CSE%20or%20implicit%20intersection.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ramon Haagen
Occasional Contributor

I want to use countifs to count a specific criteria on a certain date however in the date cell is also a timestamp. Because of this it doesn't count any other cell with this date because the time stamp is different.

 

Any idea how to get rid of the time stamp or have the countifs not count the timestamp?

 

 

3 Replies

@Ramon Haagen 

To use COUNTIFS you cannot change the datestamp as it will become an array rather than a range reference.  The way around is to test the timestamp against both the start and the end of the day.

= COUNTIFS( Date,">="&INT(Target), Date,"<"&INT(Target)+1 )

An alternative is to use an array formula to test the date only

= COUNT( IF( INT(Date)=INT(Target), 1 ) )

If you need to test every date in a list against the list, this can be done using

= COUNTIFS( Date,">="&INT(Date), Date,"<"&INT(Date)+1 )

with CSE or implicit intersection.

 

Highlighted
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies