Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Update Percent Progress Automatically Based on Two Dates?

Solved
Jump to Solution
505 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Mouser-IB-Admin
6 - Interface Innovator
6 - Interface Innovator

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?

1 Solution

Accepted Solutions

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
    )
)

See Solution in Thread

4 Replies 4

Yeap it is, check this base out

Screenshot 2024-04-02 at 10.12.53 PM.png
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

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!

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!

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
    )
)