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

JulieB

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? 

Mike Wilday

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

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

Mike Wilday

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

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

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

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

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