Formatting With a Range of Dates

Dan Elgin

I need to create a formula that looks at two different date fields and produces the result as a check (or no check) in a checkbox field.  

 

The two columns are: "Date Requested" and "Date Received."  The expectation is that the "Date Received" will be 4-6 weeks after the "Date Requested."  So basically, I need the box checked if the received date is less than 6 weeks from the requested date.  Also, the box needs to remain unchecked if the "Date Received" field is blank.

 

Ultimately, we are trying to create a "scorecard" to measure the efficiency of our various suppliers.  We will then need to filter by the supplier and calculate, by percentage, how well they are meeting expected delivery times.

 

Any takers?  Any help is greatly appreciated.

 

Dan

Data Solution Consultant

Hi Dan,

 

I am creating prototype  to understand your needs.

 

It will be helpful if you can provide sample data.

 

Is the Six week period going to be the same for each supplier ?

 

Thanks

Data Solution Consultant

Hi Dan,

 

I am considering the following in prototype :

 

(1)At present it is only one supplier for which calculations will be done

(2)6weeks will be considered for creation of Percentage Value Ranking for supplier

(3)If Date Recevied is less than 6 weeks then its color will be green

(4)If Date Received is equal to six weeks then its color will be yellow

(5)If Date Received is more than six weeks then its color will be red

(6)If Date Receipt = Date Requested then it will be 100%

(7)If Date Rceipt = 6weeks then it will be 0%

(8)If date receipt is more than six weeks then percentage value will be negative.

 

Data Solution Consultant

Prototype screenshot is attached.

Data Solution Consultant

There is a discussion for statistics-multi-projects at

https://community.smartsheet.com/discussion/statistics-multi-projetcs

 

My comment and attached screenshot in the statistics-multi-projects discussion has some similarities with the current discussion.

 

Travis

Hi Dan, try this formula:

 

=IF(ISBLANK([Date Received]2), 0, IF([Date Received]2 - [Date Requested]2 < 42, 1))

 

This will check the box if the number of days between Date Received and Date Requested is less than 42 days (6 weeks) and will remain unchecked if Date Received is blank.

 

Let me know if you need help with calculating percentages for suppliers.

Dan Elgin

Actually Travis, I'd like to take you up on that offer.  Your formula did the trick and thanks for that!!

 

Now that we are able to track which shipment are and are not meeting expected delivery dates, we can apply a % to each supplier.

 

For example: Supplier A met the 42 days or less expectation on 10 out of 10 purchase orders and are at 100%.  It doesn't matter if one shipment was 14 days and the other 9 were at 42.  As long as they are less than 43 days, they are "on time."

 

Supplier B has 8 out of 10 less than 43, but the other two are 43 or more.  They are at 80%..etc...

 

I'd like a separate tab that gathers that data from our "Master Order" list and outputs the report card data to that sheet.  

 

Can you help?  Thanks again in advance.

 

Travis

Happy to help! I built a formula that will calculate the percentage of orders that meet your criteria. Here it is:

 

=COUNTIFS(Supplier:Supplier, "Supplier A", CB:CB, 1) / COUNTIF(Supplier:Supplier, "Supplier A")

 

 

-“Supplier” is the column that contains your supplier names (“Supplier A”, “Supplier B”, etc)

-“CB” is the checkbox column that contains the formula I provided in my previous post

 

This formula will first count the number of times Supplier A is located in the Supplier column AND the corresponding checkbox is checked. Next it divides that number by a count of how many times Supplier A exists in the Supplier column. Format this column/cell as a percentage and you will have the percentage of orders that were completed in under 42 days. 

 

 

Once you have this formula created, change “Supplier A” for each supplier you want to track .

Dan Elgin

Travis - this did the trick, once again!

 

I'm having difficulty getting this to work as I pictured it.  Let me try to explain:

 

On my "Master Open Orders" sheet, I have a dropdown list column for "Supplier", which contains more than 20 different suppliers.  My vision of this was that the formula would output the resulting data to a separate "Scorecard" sheet so that we can quickly see each supplier's performance.

 

Is this possible?  Am I able to have the formula run through sheet, pick out the resulting data and output to a separate sheet?

Travis

Dan - that is exactly what you would want to do. Build the formula to run through your sheet, then use cell linking to link the cells containing the formula to the scorecard sheet. Here is an article on cell linking: http://help.smartsheet.com/customer/portal/articles/861579-cell-linking

Dan Elgin

Okay, I understand what you're saying.  I guess where I'm running into issues is how to apply the formula to the main sheet.  Looking at the attached screen shot below, you can see the "Deadline Met?" column has a percentage for the supplier "Nomar."  

 

How can I get that formula to run for every supplier, at the same time, and output that data into that column?  I'm assuming it can't be done?  Do I have to re-run the formula for each individual supplier?

 

Am I making any sense?

 

Travis

The formula would need to be duplicated for each supplier. You might consider adding a Text/Number column to your sheet where you can add these formulas (you can hide the column after creating the formulas and linking them to your second sheet). 

 

Does the Supplier Efficiency column contain this formula? If so, you can make it easier on yourself by (in row 1) replacing "Nomar" from the formula (both references) with the Row 1 Supplier cell reference. Like this:

 

=COUNTIFS(Supplier:Supplier, Supplier1, [Deadline Met?]:[Deadline Met?], 1) / COUNTIF(Supplier:Supplier, Supplier1)

 

Now, drag fill this formula down your sheet and Supplier1 will update for each row/supplier. 

Dan Elgin

Ok.  I think I've got this figured out now.  Thanks again for your help!!