#### 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
edited 12/09/19

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:

• ✭✭✭✭✭✭
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.

• 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

• ✭✭✭✭✭✭
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.

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

• 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

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

• 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.