1 Reply Latest reply on Feb 21, 2018 6:48 AM by Mark Bradbourne

    Calculate the days from current date

    Parth Shah

      Hi,

       

      I want to divide my dates into 5 categories

       

      1. Overdue - When Today() - Date < 0

       

      2. Current - When Today() - Date = 0

       

      3. 3 days - When Today() - Date is between 0 and 3 (3 Inclusive)

       

      4. 7 days - When Today() - Date is between 3 and 7 (7 Inclusive)

       

      5. 14 days - When Today() - Date is between 7 and 14 (14 Inclusive)

       

      I wrote the following formula, but am getting the wrong results. The data set is attached below along with the code i had.

       

      Code i wrote:

      If DATEDIFF('day', TODAY(), [Date]) < 0

      Then 'Overdue'

      ELSEIF DATEDIFF('day', TODAY(), [Date]) = 0

      Then 'Current'

      ELSEIF DATEDIFF('day', TODAY(), [Date]) > 0 

      Then' 3 days remaining'

      ELSEIF DATEDIFF('day', TODAY(), [Date]) >= 3 

      Then' 7 days remaining'

      ELSEIF DATEDIFF('day', TODAY(), [Date]) >= 14

      Then' 14 days remaining'

      Else  Null

      End

       

      Result i get:

      Date Number of Records

      3 days remaining  - 521

      Current  - 253

      Overdue  - 100

       

      Please help.....

        • 1. Re: Calculate the days from current date
          Mark Bradbourne

          If DATEDIFF('day', TODAY(), [Date]) < 0

          Then 'Overdue'

          ELSEIF DATEDIFF('day', TODAY(), [Date]) = 0

          Then 'Current'

          ELSEIF DATEDIFF('day', TODAY(), [Date]) < 3    //  Change this... > 0 will catch everything else

          Then' 3 days remaining'

          ELSEIF DATEDIFF('day', TODAY(), [Date]) < 14  //Change this....  >= 3 will catch everything else

          Then' 7 days remaining'

          ELSEIF DATEDIFF('day', TODAY(), [Date]) >= 14

          Then' 14 days remaining'

          Else  Null

          End