# Date function

Occasional Contributor

Hello,

thanks

8 Replies

# Re: Date function

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

# Re: Date function

Thanks @Hans Vogelaar

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

# Re: Date function

There are more blanks which are considered as open

# Re: Date function

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

# Re: Date function

"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.

# Re: Date function

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,"")

# Re: Date function

Thanks @Sergei Baklan  you are right

# Re: Date function

Thanks @Hans Vogelaar so much

you are right

Thanks a million