How can I subtract a changing number of days from a date?

✭✭✭

Hi there,

I have a "Due Date" column and an "Order By Date" column. The Order By needs to reflect a date that is the Due Date minus our lead time (reflected in a third column and listed as a number of days). The issue is that the lead times change and vary from product to product. So I'm trying to create a formula that takes "Due Date" - "Lead Time" = "Order By". I'm hoping this is achievable?

Tags:

Try this formula in your Order By column...assuming your column names are Due Date and Lead Time

Try this formula in your Order By column...assuming your column names are Due Date and Lead Time

• Hi! I actually do exactly this for my projects all the time. If you have a column specified as a date column you can actually just used =[Due Date]@row-[Lead Time]@row) and it should calculate that date for you. Just make sure its set as a date column, and that your schedule is set to use M-F as work days.

• ✭✭✭

Thank you! I was thinking I needed to use a Function and couldn't figure out why it kept rejecting me. haha

You guys are awesome :D

• Hi,

I'm trying to use the formula above but it just equals 0. I need to calculate no of days which is column LT form the specific date which is FAD. I used both + and - and none is working.

Thank you!

With Date type of values, you don't need to use a SUM function. Try simply adding the number to the Date:

Note that the column you're putting the formula into would also need to be a Date type of column.

Cheers,

Genevieve

• Great now it worked! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!