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
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
Thank you ! worked like a charm
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.