SOLVED
Home

Countifs and how to ignore timestamp

%3CLINGO-SUB%20id%3D%22lingo-sub-401253%22%20slang%3D%22en-US%22%3ECountifs%20and%20how%20to%20ignore%20timestamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401253%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-401253%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%3CLINGO-SUB%20id%3D%22lingo-sub-401274%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20and%20how%20to%20ignore%20timestamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401274%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%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401269%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20and%20how%20to%20ignore%20timestamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401269%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%3BSuper%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401268%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20and%20how%20to%20ignore%20timestamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401268%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%20above%20formula%20was%20only%20mock-up%2C%20you%20shall%20use%20your%20actual%20ranges%20and%20criteria%20cells.%20In%20your%20case%20that's%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTIFS(tbl_Activity_registration_1%5BSaved_on%5D%2C%22%26gt%3B%3D%22%26amp%3B%24A3%2Ctbl_Activity_registration_1%5BSaved_on%5D%2C%22%26lt%3B%22%26amp%3B%24A3%2B1)%3C%2FPRE%3E%0A%3CP%3EBut%20I%20don't%20see%20any%20time%20with%20your%20dates%2C%20thus%20that%20could%20be%20slimly%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTIFS(tbl_Activity_registration_1%5BSaved_on%5D%2C%24A2)%3C%2FPRE%3E%0A%3CP%3E(column%20Y%20in%20attached)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401265%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20and%20how%20to%20ignore%20timestamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401265%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%3BThanks%20for%20the%20advice.%20However%20I%20have%20been%20trying%20to%20make%20it%20work%20for%20the%20last%20hour%20without%20much%20success%20and%20I%20do%20not%20understand%20why.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20file(password%201234).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasicly%20in%20sheet%20productivity%20report%20in%20cell%20E2%20I%20would%20like%20that%20the%20formula%20counts%20from%20sheet%20acces%20database%20column%20D%20the%20amount%20of%20number%201's%20on%20the%20specific%20date%20are%20in%20there.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20help%20me%20with%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401254%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20and%20how%20to%20ignore%20timestamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401254%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%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%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Excel%20dates%20are%20integer%20numbers%20and%20the%20time%20is%20decimal%20part.%20Thus%2C%20to%20count%20dates%20only%20you%20may%20use%20something%20like%3C%2FP%3E%0A%3CPRE%3EINT(%26lt%3Bdate%26gt%3B)%3C%2FPRE%3E%0A%3CP%3Ein%20your%20formula.%20If%20apply%20to%20the%20range%2C%20you%20may%20use%20SUMPRODUCT.%20If%20with%20COUNIFS%2C%20that%20could%20be%20like%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTIFS(%26lt%3Brange%26gt%3B%2C%22%26gt%3B%3D%22%26amp%3B%26lt%3Bdate%26gt%3B%2C%26lt%3Brange%26gt%3B%2C%22%26lt%3B%22%26amp%3B%26lt%3Bdate%26gt%3B%2B1)%3C%2FPRE%3E%0A%3CP%3Esince%20you%20can%C3%BD%20apply%20INT%20to%20the%20range.%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?

 

 

5 Replies

Hi @Ramon Haagen ,

 

In Excel dates are integer numbers and the time is decimal part. Thus, to count dates only you may use something like

INT(<date>)

in your formula. If apply to the range, you may use SUMPRODUCT. If with COUNIFS, that could be like 

=COUNTIFS(<range>,">="&<date>,<range>,"<"&<date>+1)

since you caný apply INT to the range.

@Sergei Baklan Thanks for the advice. However I have been trying to make it work for the last hour without much success and I do not understand why.

 

I have attached the file(password 1234).

 

Basicly in sheet productivity report in cell E2 I would like that the formula counts from sheet acces database column D the amount of number 1's on the specific date are in there. 

 

Could you help me with this?

Highlighted
Solution

@Ramon Haagen , above formula was only mock-up, you shall use your actual ranges and criteria cells. In your case that's

=COUNTIFS(tbl_Activity_registration_1[Saved_on],">="&$A3,tbl_Activity_registration_1[Saved_on],"<"&$A3+1)

But I don't see any time with your dates, thus that could be slimly

=COUNTIFS(tbl_Activity_registration_1[Saved_on],$A2)

(column Y in attached)

 

 

@Sergei Baklan Super thanks!

@Ramon Haagen , you are welcome

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies