Date function

%3CLINGO-SUB%20id%3D%22lingo-sub-1569525%22%20slang%3D%22en-US%22%3EDate%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569525%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecould%20you%20please%20help%20with%20the%20question%20in%20the%20file%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1569525%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1569900%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569900%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747134%22%20target%3D%22_blank%22%3E%40Ahmad920%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS(D14%3AD963%2C%22%26lt%3B%22%26amp%3BTODAY()-100%2CE14%3AE963%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570130%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570130%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20when%20I%20use%20your%20formula%20I%20get%26nbsp%3B%20397%26nbsp%3B%20while%20there%20is%20only%207%20dates%20in%202020%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570166%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570166%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747134%22%20target%3D%22_blank%22%3E%40Ahmad920%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20rest%20are%20blanks%20which%20are%20considered%20as%20open%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570181%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570181%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%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%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eactually%202%20conditions%20have%20to%20be%20met%20together%3A%3C%2FP%3E%3CP%3E1-%20case%20open%3C%2FP%3E%3CP%3E2-%20date%3A%20in%20the%20last%20100%20days%3C%2FP%3E%3CP%3Eso%20the%20answer%20should%20be%206%20%2C%20but%20I%20cant%20get%20this%20result%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570462%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570462%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747134%22%20target%3D%22_blank%22%3E%40Ahmad920%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20request%20was%3C%2FP%3E%0A%3CP%3E%224.%20number%20of%20the%20cases%20which%20still%20open%20for%20more%20than%20100%20day%20%2C%20using%20formula%22%3C%2FP%3E%0A%3CP%3EThere%20are%20397%20such%20cases%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S3446.png%22%20style%3D%22width%3A%20505px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210617iA73BA976B73BC6AB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22S3446.png%22%20alt%3D%22S3446.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EI%20don't%20see%20how%20you%20arrived%20at%206.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570466%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747134%22%20target%3D%22_blank%22%3E%40Ahmad920%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOh%20wait%2C%20your%20request%20was%20incorrect.%20You%20don't%20want%20cases%20that%20have%20been%20open%20for%20%3CSTRONG%3Emore%3C%2FSTRONG%3E%20than%20100%20days%2C%20but%20instead%20cases%20that%20have%20been%20open%20for%20%3CSTRONG%3Eless%3C%2FSTRONG%3E%20than%20100%20days!%3C%2FP%3E%0A%3CP%3EThat%20is%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS(D14%3AD963%2C%22%26gt%3B%22%26amp%3BTODAY()-100%2CE14%3AE963%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570561%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570561%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%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%20you%20are%20right%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570562%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570562%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bso%20much%3C%2FP%3E%3CP%3Eyou%20are%20right%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20million%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello,

 

could you please help with the question in the file attached

 

thanks

8 Replies
Highlighted

@Ahmad920 

=COUNTIFS(D14:D963,"<"&TODAY()-100,E14:E963,"")

Highlighted

Thanks @Hans Vogelaar 

but when I use your formula I get  397  while there is only 7 dates in 2020

Highlighted

@Ahmad920 

There are more blanks which are considered as open

Highlighted

Thanks @Sergei Baklan 

 

actually 2 conditions have to be met together:

1- case open

2- date: in the last 100 days

so the answer should be 6 , but I cant get this result

 

Highlighted

@Ahmad920 

Your request was

"4. number of the cases which still open for more than 100 day , using formula"

There are 397 such cases:

S3446.png

I don't see how you arrived at 6.

Highlighted

@Ahmad920 

Oh wait, your request was incorrect. You don't want cases that have been open for more than 100 days, but instead cases that have been open for less than 100 days!

That is

=COUNTIFS(D14:D963,">"&TODAY()-100,E14:E963,"")

Highlighted

Thanks @Sergei Baklan  you are right

Highlighted

Thanks @Hans Vogelaar so much

you are right 

Thanks a million