3 Replies Latest reply on Sep 17, 2018 3:29 AM by Jim Horbury

    Calculate and flag number of days late based on current date

    Moses Dhas

      Hi,

       

      i have several date based columns in Excel. Each Date column has 4 type (Start Original, Start Plan, Start Forecast Start Actual & IDC Original, IDC Plan, IDC Forecast & IDC Actual etc ) and would like help on the following:

       

      1. For Start Type Get count of total rows with the following criteria
        1. Start Actual is null
        2. Start Forecast is less than Today
        3. A Calculated field to have the number of days difference between Today and Start forecast, and mark them as a) 0 - 7 days, b) 8 - 14 days c) Greater than 21 days
      2. For IDC Type Get count of total rows with the following criteria
        1. IDC Actual is null
        2. IDC Forecast is less than Today
        3. A Calculated field to have the number of days difference between Today and Start forecast, and mark them as a) 0 - 7 days, b) 8 - 14 days c) Greater than 21 days

       

      Output should be like:

       

      Start Forecast (Late)IDC ForecastFlagNumber of Days
      4Late by 0 - 7 days1
      5Late by 8 - 14 days5
      10Late more than 14 days21
      20Late by 8 - 14 days4
      3Late more than 14 days

       

       

      thanks

        • 1. Re: Calculate and flag number of days late based on current date
          Jim Horbury

          Hi. I'm assuming you want to express this logic as a single calculated field? Have you tried something like this (example for the start type calculation)

           

          IF (ISNULL([Start Actual]) AND [Start Forecast] < TODAY())

              THEN (

                  IF DATEDIFF('day',TODAY(),[Start Forecast]) < 7 THEN '0-7'

                  ELSEIF DATEDIFF('day',TODAY(),[Start Forecast]) < 14 THEN '8-14'

                  ELSE '21+'

              END

              )

          END

          • 2. Re: Calculate and flag number of days late based on current date
            Moses Dhas

            Thanks for the assist, however the formula returns 0 for everything. i have created a formula (refer attached). based on the formula [Start Fcst (Count)] should i create another one to flag based on number of day if this is the case i will have to do 2 formula for each date type and there are 14 in total.

             

            Formula created

             

            INT(If DATEDIFF('day', TODAY(), [Start Forecast]) <= 0 and ISNULL([Start Actual])= TRUE       

            Then [Start Forecast]

            ELSE

            null

            END)

            • 3. Re: Calculate and flag number of days late based on current date
              Jim Horbury

              The calculation returns nothing because there are no rows that satisfy the first conditions of your required calculation:

               

              ISNULL([Start Actual]) AND [Start Forecast] < TODAY()

               

              Could this be OR?

               

              Regards your calculation, it depends if you need to display the actual DATEDIFF value or just aggregate the number of rows in each category (0-7, 8-14, etc). If it's the latter, then you can handle it in a single calculation.