Update Percent Progress Automatically Based on Two Dates?

[Mouser-IB-Admin]

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?

[TheTimeSavingCo]

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

[Mouser-IB-Admin]

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!

[TheTimeSavingCo]

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!

[Mouser-IB-Admin · Accepted solution]

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