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.

Is it possible to set due dates based on a formula?

Options
JulieB
JulieB
edited 12/09/19 in Archived 2017 Posts

Hello, 

I've imported an Excel sheet and my formulas aren't working. I'd like to automate due dates where the duration may change based on a numerical figure. For example, if there are 10 items to move, it will take 2 days for each of 4 tasks, but 100 items to move will take 5 days for each of 4 tasks.

I've been playing with IF, OR, AND, WORKDAY, and keep getting #INVALID DATA TYPE." Any brilliant ideas? 

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Can you post some examples of your formulas? And maybe a screenshot of where you are trying to put them in your sheet? Invalid data type usually infers that you are getting data that is not for the correct column type. 

    https://help.smartsheet.com/articles/2476176-formula-error-messages see that for more information on your errors. 

  • JulieB
    Options

    Ok, so the [# Assets] column is the amount of items to be done. That corresponds to [Number of Days Estimate per Major Step] with this formula: 

    =IF(AND([# Assets]2>=1, [# Assets]2<=25), "2",IF(AND([# Assets]2>=26, [# Assets]2<=50),"3",IF(AND([# Assets]2>=51,[# Assets]2<=100),"4",IF(AND([# Assets]2>=101,[# Assets]2<=200),"5",IF(AND([# Assets]2>=201,[# Assets]2<=300),"6",IF(AND([# Assets]2>=301,[# Assets]2<=400),"7",IF(AND([# Assets]2>=401,[# Assets]2<=500),"8",IF(AND([# Assets]2>=501,[# Assets]2<=600),"9",IF(AND([# Assets]2>=601,[# Assets]2<=1000),"10",""))))))))) 

    That works. 

    Then, I'd like it to use that [Number of Days...] in [Vendor Task 1 Due]. It should be adding [Number of Days...] to [Sent to Vendor], counting only workdays, and putting the new due date in [Vendor Task 1 Due]. Excel formula was: 

    =IF(OR([Sent to Vendor]2 = 0, [Sent to Vendor]2 = "", [Sent to Vendor]2 = "N/A"), "", WORKDAY([Sent to Vendor]2, [Task 2 Due]9[Number of days Estimate per Major Step]2))

    This is where I can't get it work. I appreciate your help!

    Julie

    SampleSS.PNG

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Is the Vendor Task 1 Due column a Date Column type? I would right-click on the column headers and choose - Column Properties and make sure they are date columns. 

  • JulieB
    Options

    Well, it is now! But it's still showing #UNPARSEABLE. I tried copying and pasting the formula to another cell and that doesn't help. 

    [Sent to Vendor] was already set as a Date Column Type. Would it maybe help to do something with ISDATE? 

  • JulieB
    Options

    Even just trying something simpler like 

    =WORKDAY([Sent to Vendor]2, [Number of days Estimate per Major Step]2)

    gives me the #INVALID DATA TYPE

    Julie

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Couple things to check... 

    Does [Sent to Vendor]2 contain a date?

    Also, try converting the data from Number of days... to an integer with the INT formula. 

    =WORKDAY([Sent to Vendor]2, INT([Number of days Estimate per Major Step]2))

  • JulieB
    Options

    Sorry for the delay, was out sick. Sent to Vendor column is set to date. Changing the formula to include INT still didn't help. 

    Ah, well. 

    Thanks for the help!

    Julie

This discussion has been closed.