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?
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?
Comments

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/2476176formulaerrormessages see that for more information on your errors.

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

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

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?

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

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))

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