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

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

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

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

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.