Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 02, 2024 06:26 AM
I'm not sure if I'm looking for a formula or if what I'm wanting is even possible, so this is just a general question about utilizing the progress functionality for percent fields.
What I'm hoping to accomplish is a percent field, set to view as progress, that updates based on the progress between two date fields. So for example, if date 1 (0%) is January 1st, 2024 and date 2 (100%) is December 31st, 2024, I'm wanting the percent progress to update as the year progresses to show as it gets closer to date 2.
The progress function is great but if it were able to automatically update for tasks that have start/end dates that would make it all the more powerful. Is this possible?
Solved! Go to Solution.
Jul 16, 2024 04:18 AM
Thanks, that got me the rest of the way! I was able to use that framework to tweak it a bit, and now it doesn't rely on a "Today" field but instead uses the TODAY() function. It also now shows as 100% when the end/expiration date has passed. I'll include my updated formula below for reference.
IF(
AND(
DATETIME_DIFF(TODAY(), {Beginning Date}, 'days') > 0,
DATETIME_DIFF(TODAY(), {Expiration Date}, 'days') <= 0
),
DATETIME_DIFF(TODAY(), {Beginning Date}, 'days') / DATETIME_DIFF({Expiration Date}, {Beginning Date}, 'days'),
IF(
DATETIME_DIFF(TODAY(), {Expiration Date}, 'days') > 0,
1
)
)
Apr 02, 2024 07:14 AM
Yeap it is, check this base out
You'll probably need to add more error checks though, right now I'm only checking whether today is before the end date and after the start
Jul 15, 2024 06:24 AM
Good morning! Apologies for the tardy reply, but I'm just circling back to some open Community posts.
What you showed looks like it might work, but when viewing the base I can't see the formula behind the field that's calculating the progress. Can you share how you built that field so I can test?
Thanks!
Jul 15, 2024 08:08 PM
Yeap, sure! Here's the formula:
IF(
AND(
DATETIME_DIFF(
Today,
Start,
'days'
) > 0,
IS_BEFORE(
Today,
End
)
),
DATETIME_DIFF(
Today,
Start,
'days'
)
/
DATETIME_DIFF(
End,
Start,
'days'
)
)
You can also get access to the formula by duplicating the base into your workspace!
Jul 16, 2024 04:18 AM
Thanks, that got me the rest of the way! I was able to use that framework to tweak it a bit, and now it doesn't rely on a "Today" field but instead uses the TODAY() function. It also now shows as 100% when the end/expiration date has passed. I'll include my updated formula below for reference.
IF(
AND(
DATETIME_DIFF(TODAY(), {Beginning Date}, 'days') > 0,
DATETIME_DIFF(TODAY(), {Expiration Date}, 'days') <= 0
),
DATETIME_DIFF(TODAY(), {Beginning Date}, 'days') / DATETIME_DIFF({Expiration Date}, {Beginning Date}, 'days'),
IF(
DATETIME_DIFF(TODAY(), {Expiration Date}, 'days') > 0,
1
)
)