Forum Discussion

Ahmad920's avatar
Ahmad920
Copper Contributor
Aug 06, 2020

Date function

Hello,   could you please help with the question in the file attached   thanks
excel
Ahmad920's avatar
Ahmad920
Copper Contributor
Aug 06, 2020

Thanks HansVogelaar 

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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 06, 2020

Ahmad920 

There are more blanks which are considered as open

  • Ahmad920's avatar
    Ahmad920
    Copper Contributor
    Aug 06, 2020

    Thanks SergeiBaklan  you are right

  • Ahmad920's avatar
    Ahmad920
    Copper Contributor
    Aug 06, 2020

    Thanks SergeiBaklan 

     

    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

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Aug 06, 2020

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

      • Ahmad920's avatar
        Ahmad920
        Copper Contributor
        Aug 06, 2020

        Thanks HansVogelaar so much

        you are right 

        Thanks a million 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Aug 06, 2020

      Ahmad920 

      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.

Resources