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