Calculate start date based on set end date

[Kelly_McInnes]

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!

[JonathanBowen]

Hi @Kelly_McInnes - try this:

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

Slightly non-intuitive as you are adding negative days :slightly_smiling_face:

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

[Kelly_McInnes]

Thank you ! worked like a charm :slightly_smiling_face: