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

rfrimberger

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?

Amy Vanderheyden

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

=[Due Date]@row - [Lead Time]@row

Amy Vanderheyden

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

=[Due Date]@row - [Lead Time]@row

Ben Shelby

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.

rfrimberger

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

VanessaBjrndr

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!

Genevieve P.

Hi @VanessaBjrndr

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

=FAD@row + LT@row

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

See: Use Formulas to Perform Calculations With Dates

Cheers,

Genevieve

VanessaBjrndr

Great now it worked! Thank you!