Number of days delay and chart

[Thomas Kuriakose]

Respected Sirs,

We have a project tracker, where we need to calculate the delivery date (column F) based on the below parameter.

1. Acceptance date (column D) + no of days in lead time matrix (K3:L5) with respect to number of units (column B) per project (column E).
2. Friday and Saturday are holidays and this should be excluded to arrive at the delivery date.

We need to also calculate the number of days delay for overdue and due.

Kindly guide on how this can be done with formula.

We also need a chart project wise in which the series need to be colored as per the average score per project (N13:O24) and this needs to be updated dynamically based on data entry, the current example has only 30 rows of data.

Can the summary of projects be done with formula.

The color scheme is as follows -

Average score equal to 7 = Green
Average score between 4 and 6 = Yellow
Average score less than 4 = Red

i have entered the values after manual counts. kindly find attached the file for your reference.

Thank you very much,

with regards,
Thomas

 

[r2c2]

@Thomas Kuriakose

You are at 670 posts now and still not using tables for your analysis and reporting? Consider that a slap on your wrist

Calculation of lead time:

First up, start by setting up both your project data and leadtime mapping as tables. While at it, split the leadtime mapping to 3 columns (start, end and no.of days). This way you can write lookups easily.

Assuming you have a leadtime table with 3 columns, use this formula in Delivery Date column:

=WORKDAY.INTL([@[Acceptance Date]],SUMIFS(leadtime[No. of days], leadtime[Start],">="&[@[Project Count]],leadtime[End],"<="&[@[Project Count]]),7)

Change other formulas to structural references while at it.

Project status graph:

If you have access to Power Pivot (Excel 2016?) then this would be so much easier. Assuming you don't have it, create a regular pivot from your project data, with project ref in row labels and average score in values area.

Now just add 3 helper columns to split the score to Red / Amber / Green columns and set up dynamic named ranges for each. Read this for more:

https://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

Yes, it is almost 9 years old but it is gold

See attached workbook. But may be don't see and try to recreate it using the instruction? You will learn more for sure.

 

[Thomas Kuriakose]

@r2c2

Respected Sir,

Good day.

My sincere apologies for not having used tables. I am ashamed of this inefficiency from my end.

I still feel my effort put into learning excel is not at all sufficient.

Thank you so much for all the solutions provided.

Sir, one question, the delivery date is not adding the number of days to the acceptance date.

For example, the first project - AB17-152, the delivery date should be 16.01.2018 (since the number of units are within 1-7) so it should add 7 days and also exclude the Friday and Saturday if it occurs.

For the charts, thank you for the amazing insight.

Much appreciated.

with regards,
thomas

 

[bosco_yip]

In Column F "Delivery Date" F2, formula copied down :

=WORKDAY.INTL(D2,LOOKUP(B2,{1,7;8,14;16,21}),7)

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this solution.

Much much appreciated,

with regards,
thomas