Aug 05 2020 05:14 PM
Hello,
could you please help with the question in the file attached
thanks
Aug 06 2020 01:48 AM
Thanks @Hans Vogelaar
but when I use your formula I get 397 while there is only 7 dates in 2020
Aug 06 2020 02:12 AM - edited Aug 06 2020 02:14 AM
There are more blanks which are considered as open
Aug 06 2020 02:20 AM
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
Aug 06 2020 05:03 AM
Your request was
"4. number of the cases which still open for more than 100 day , using formula"
There are 397 such cases:
I don't see how you arrived at 6.
Aug 06 2020 05:06 AM
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,"")