Reference a date based on Percentage complete

EmilyH

In Sheet One, there are 25 products assigned to 4 Product Types. Each Product has an expected delivery and an actual delivery date.

In Sheet Two, we want to add the dates associated with the percentage complete.

The existing formulas provide the percentages and totals but how can I add the dates for the milestones. For example, what is the date that 25% of Product Type Three was delivered. It should be:

25% Delivered = 10/14/19

50% Delivered = 10/15/19

75% Delivered = 10/17/19

100% Delivered = 10/18/19

Thank you for your help.

Paul Newcome

You would need to establish a "Start Date". A set date for each row that can be used for comparisons.

 

Then you would take your total number of days

 

=([End Date]@row - [Start Date]@row)

 

and multiply it by your percentage

 

=([End Date]@row - [Start Date]@row) * .25

 

This would give you the NUMBER of days it would take to reach that percentage.

Round, round up, round down, take the integer, leave as is... Which one you choose is up to you, but you would then take that number and add it to the start date. That would give you the date that is at the 25% mark.

 

=([End Date]@row - [Start Date]@row) * .25 + [Start Date]@row

EmilyH

Hi Paul,

Thank you for the formula, I was able to calculate the Projected Delivery Dates for each quarter.

I am still unable to find the max date for each Actual Delivery Date for each quarter. The actual dates do not match the projected and sometimes deliveries arrive sooner or later than expected.

I was able to create a formula to identify if a quarter of the actual deliveries arrived:

=IF((COUNTIFS({Sheet

One_Find date_Actual Date}, (ISDATE(@cell)), {Sheet One_Find

date_Product Type}, [Product Type]1) / [Total Deliverables]1) >= 0.25, "YES")

I tried creating a MAX(COLLECT) formula to pull the MAX date for 25% complete but it pulled the MAX date for ALL actual deliveries:

=MAX(COLLECT({Sheet

One_Find date_Actual Date}, {Sheet One_Find date_Product Type}, @cell = [Product Type]1), (COUNTIFS({Sheet

One_Find date_Actual Date}, (ISDATE(@cell)), {Sheet One_Find

date_Product Type}, [Product Type]1) / (COUNTIF({Sheet

One_Find date_Product Type}, {Sheet One_Find date_Product Type} = [Product Type]1))) >= 0.25)

I see what I did wrong, having the IF statement at the end doesn’t affect what is ‘Collected’ and displayed. When I move the IF statement to appear before the MAX(COLLECT), it also does not work because it is searching for the date IF the last date is less than 25%. If the Max date is greater than 25% complete, it is blank.

=IF((COUNTIFS({Sheet

One_Find date_Actual Date}, (ISDATE(@cell)), {Sheet One_Find

date_Product Type}, [Product Type]1) / [Total Deliverables]1) <= 0.25, MAX(COLLECT({Sheet

One_Find date_Actual Date}, {Sheet One_Find date_Product Type}, @cell = [Product Type]1)))

How can I show the MAX Actual Delivery Date for a percentage (ie. less than 25%) if I only know the number of Deliverables? I can’t use a Start and Finish date for Actual Dates since we don’t know the final Actual (end) Delivery date. For example, we received 25% of Type ONE actual deliveries on 10/14/19.

I hope that makes sense. Thank you for your help!

Emily

Paul Newcome

How are you calculating your Projected Delivery Date?

EmilyH

"Expected #Days to Complete 25%" =([Last Projected Delivery Date]@row - [Expected First Delivery]@row) * 0.25

"Expected: 25% of Product Delivered (Date)" =[Expected First Delivery]1 + ([Expected #Days to Complete 25%]1 * 1)

"Expected: 50% of Product Delivered (Date)" =[Expected First Delivery]1 + ([Expected #Days to Complete 25%]1 * 2)

"Expected: 100% of Product Delivered (Date)" =[Expected First Delivery]1 + ([Expected #Days to Complete 25%]1 * 4)

I cannot use this formula for Actual Delivery Date because it is not static like the Projected forecasted date. For example, for Type ONE, every 7 days I should expect 25% of the receipts based on the Projected delivery (forecast). The Actual date for 25% of the items might occur on Day 8. The ask is to provide a date for when 25% of the deliverables were completed. At this time, we are not comparing Forecast verses Actual dates, we just want to know (for Type ONE) when 25% of the 7 items were received.

Thank you!

Paul Newcome

Ok. So how are you tracking that deliverables are completed?

EmilyH

In Sheet ONE, column Actual Delivery Date is where we are manually tracking the receipt date for each completed (aka 'delivered and received') Product #ID for each Project Type (1-4).

Sheet TWO is the summary report based on Sheet ONE data.

Please let me know if you want me to post Sheet TWO again, the screen shot is in the original post but it is very small.

Paul Newcome

Ok. After a little bit of studying, I THINK I am finally understanding things.

.

Sheet One's [Product] column is the deliverable. [Product Type] is basically just a category. So it could also be restated as...

 

[Product Type] ONE contains the deliverables as 1, 4, 10, 15, 20, 23, and 25.

.

I am going to say [Product Type] ONE also has the deliverable of 26. This will give it 8 deliverables which makes for easier math.

.

You are looking for the date when 25%, 50%, 75%, and 100% of those deliverables were met.

 

So you want the second, fourth, sixth and eighth dates for this particular [Product Type].

 

Since the number of deliverables is variable, obviously it will not always be 2, 4, 6, and 8. I am just using this for an example in order to make sure I understand exactly what is going on. In all reality we need to base them off of the percentages instead of specific numbers.

.

Am I finally understanding correctly, or am I still way off base?

EmilyH

Yes Paul, that is correct.

Thanks for your time!

Paul Newcome

Phew. Sorry for being so slow on the uptake.

 

Ok. I have a few ideas. Let me do some testing, and I will get back to you.

Thanks for your patience while I was busy being dense. Haha

Paul Newcome

Quick question:

 

Let's say you have 7 deliverables (eg. Type ONE)... 25% is 1.75. Do you want it to be 1 or 2. Similarly, 50% is 3.5 and 75% is 5.25. Are you wanting to round up or round down?

Paul Newcome

So if you are rounding up, here are the formulas. If you wanted to round down, just replace the ROUNDUP function with ROUNDDOWN, and you'll be good to go. In the Actual Date formulas, they are written to just leave the cell blank if that milestone has not yet been met.

.

.

25% Expected Date:

=SMALL(COLLECT({SOURCE - Projected Delivery Date}, {SOURCE - Product Type}, $[Product Type]@row), ROUNDUP($[Total Deliverables]@row * 0.25))

.

50% Expected Date:

=SMALL(COLLECT({SOURCE - Projected Delivery Date}, {SOURCE - Product Type}, $[Product Type]@row), ROUNDUP($[Total Deliverables]@row * 0.5))

.

75% Expected Date:

=SMALL(COLLECT({SOURCE - Projected Delivery Date}, {SOURCE - Product Type}, $[Product Type]@row), ROUNDUP($[Total Deliverables]@row * 0.75))

.

100% Expected Date:

=SMALL(COLLECT({SOURCE - Projected Delivery Date}, {SOURCE - Product Type}, $[Product Type]@row), $[Total Deliverables]@row)

.

.

25% Actual Date:

=IF(COUNTIFS({SOURCE - Actual Delivery Date}, ISDATE(@cell), {SOURCE - Product Type}, $[Product Type]@row) >= ROUNDUP($[Total Deliverables]@row * 0.25), SMALL(COLLECT({SOURCE - Actual Delivery Date}, {SOURCE - Product Type}, $[Product Type]@row), ROUNDUP($[Total Deliverables]@row * 0.25)))

.

50% Actual Date:

=IF(COUNTIFS({SOURCE - Actual Delivery Date}, ISDATE(@cell), {SOURCE - Product Type}, $[Product Type]@row) >= ROUNDUP($[Total Deliverables]@row * 0.5), SMALL(COLLECT({SOURCE - Actual Delivery Date}, {SOURCE - Product Type}, $[Product Type]@row), ROUNDUP($[Total Deliverables]@row * 0.5)))

.

75% Actual Date:

=IF(COUNTIFS({SOURCE - Actual Delivery Date}, ISDATE(@cell), {SOURCE - Product Type}, $[Product Type]@row) >= ROUNDUP($[Total Deliverables]@row * 0.75), SMALL(COLLECT({SOURCE - Actual Delivery Date}, {SOURCE - Product Type}, $[Product Type]@row), ROUNDUP($[Total Deliverables]@row * 0.75)))

.

100% Actual Date:

=IF(COUNTIFS({SOURCE - Actual Delivery Date}, ISDATE(@cell), {SOURCE - Product Type}, $[Product Type]@row) >= $[Total Deliverables]@row, SMALL(COLLECT({SOURCE - Actual Delivery Date}, {SOURCE - Product Type}, $[Product Type]@row), $[Total Deliverables]@row))

EmilyH

Amazing! Thank you so much for your help, it worked.

Can I ask another favor, I posted another formula-related discussion but I forgot to add screen shots, so it looks like someone has replied to my post but no one has reviewed yet. Can you please check it out and redirect or help me with another formula?

https://community.smartsheet.com/discussion/index-match-and-formula-0

Thank you so much!

Emily

Paul Newcome

I will take a look and reply there.