Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formatting With a Range of Dates

Dan Elgin
Dan Elgin ✭
edited 12/09/19 in Archived 2015 Posts

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

Tags:
· Share on FacebookShare on Twitter

Comments

  • Data Solution Consultant
    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

    · Share on FacebookShare on Twitter
  • Data Solution Consultant
    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.

     

    · Share on FacebookShare on Twitter
  • Data Solution Consultant
    Data Solution Consultant ✭
    edited 03/04/16

    Prototype screenshot is attached.

    Dates_Dan.png

    · Share on FacebookShare on Twitter
  • Data Solution Consultant
    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.

     

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    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.

    · Share on FacebookShare on Twitter
  • Dan Elgin
    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.

     

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    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 .

    · Share on FacebookShare on Twitter
  • Dan Elgin
    Dan Elgin ✭
    edited 07/10/15

    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?

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    edited 07/13/15

    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

    · Share on FacebookShare on Twitter
  • Dan Elgin
    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?

     

    Capture.PNG

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    edited 07/14/15

    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. 

    · Share on FacebookShare on Twitter
  • Dan Elgin
    Dan Elgin ✭

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

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories