 Calculate start date based on set end date

Hi all! I need to create a formula for a changing start date based on a static end date and variable days to complete task.

Eg Ten people need to be at a destination on {target date}(date field) but it takes differing numbers of days to get there {days to target}(number field), so I need to calculate each person’s start date(date field) ie {target date} - {days to target}. I’ve tried a few different date formulas and variations and keep coming up with errors!

Hi @Kelly_McInnes - try this:

`DATEADD({Target Date}, (-1 * {Days to Target}), 'days')`

Slightly non-intuitive as you are adding negative days If you want to include the start date and the end date within the number of days to target you might want to use the alternative formula:

`DATEADD({Target Date}, (-1 * {Days to Target}) + 1, 'days')`

In the case of the example “John” above, there are 8 days between the 24/5 and 31/5 including both end points. With the first formula you’re saying “start on the 23rd go for 8 days, which takes you to the 30th; the next day is the 31st”.

JB

1 Like

Thank you ! worked like a charm 