Progress Bar Date Automation

Pippa

Hi All,

Could someone help me figure out a formula for the progress bar column style.

My goal is to determine how close (by progress bar visual) are we to the "target end date" of a task.

For example, my start date is 01/09/2022 and my target end date is 30/09/2022 and the actual date of today is the 29/09/2022 so we would be at 99% right? which means the progress bar would basically be full.

Does that make sense? attached pic of the sheet

Paul Newcome

In that case we would start by subtracting the start date from today to see how far along we are.

=TODAY() - [Start Date]@row

Subtracting the Start Date from the end date will give us the total number of days.

=[Target end Date]@row - [Start Date]@row

Dividing the first by the second will give us our percentage.

=(TODAY() - [Start Date]@row) / ([Target end Date]@row - [Start Date]@row)

Then we put that into a nested IF to say less than 25% = "Empty", less than 50% = "Quarter", so on and so forth.

=IF((TODAY() - [Start Date]@row) / ([Target end Date]@row - [Start Date]@row)< 0.25, "Empty", IF((TODAY() - [Start Date]@row) / ([Target end Date]@row - [Start Date]@row)< 0.50, "Quarter", IF((TODAY() - [Start Date]@row) / ([Target end Date]@row - [Start Date]@row)< 0.75, "Half", IF((TODAY() - [Start Date]@row) / ([Target end Date]@row - [Start Date]@row)< 1, "Three Quarter", "Full"))))

Pippa

Yes that's it perfect!!! Thank you so much! looks better than a Gantt

Paul Newcome

What would be the percentages you would expect for each of the different statuses?

Unfortunately you won't be able to fill the bar between what you see there, so you would need to determine which percentage the bar goes from empty to quarter, from quarter to half, so on and so forth.

Have you checked out Gantt view? You can leave the dependencies turned off but still show the start and end dates as well as the percent complete, and that percent complete bar does increment based on the percentage.

Pippa

Thanks Paul for your reply!

I was thinking pretty much all formula based. For example, if start date is 01/09/22 and target end date is 30/09/2022 then the difference between those dates is 29 days which would be your 100%. So in progress bar terms the closer you get to 29 (or 30/09/22) the closer you would be to "full". Using rounding that would be:

100% would be 22-29 days, and "full"

75% would be 15-22, and "3 quarter"

50% would be 15 days, and "half"

25% would be 7-15 days, and "quarter"

0% would be 1-7 days, and "empty"

My main issue is that I can't use static days, as the task won't always be a length of 29 days. I don't mind creating more columns that calculate and then hide them...

I was thinking of Gantt, but I want this to be in a sheet with a lot of other info, so I just want this to be a quick glance that doesn't take up half your screen if you see what I mean?

Paul Newcome

In that case we would start by subtracting the start date from today to see how far along we are.

=TODAY() - [Start Date]@row

Subtracting the Start Date from the end date will give us the total number of days.

=[Target end Date]@row - [Start Date]@row

Dividing the first by the second will give us our percentage.

=(TODAY() - [Start Date]@row) / ([Target end Date]@row - [Start Date]@row)

Then we put that into a nested IF to say less than 25% = "Empty", less than 50% = "Quarter", so on and so forth.

=IF((TODAY() - [Start Date]@row) / ([Target end Date]@row - [Start Date]@row)< 0.25, "Empty", IF((TODAY() - [Start Date]@row) / ([Target end Date]@row - [Start Date]@row)< 0.50, "Quarter", IF((TODAY() - [Start Date]@row) / ([Target end Date]@row - [Start Date]@row)< 0.75, "Half", IF((TODAY() - [Start Date]@row) / ([Target end Date]@row - [Start Date]@row)< 1, "Three Quarter", "Full"))))

Pippa

Yes that's it perfect!!! Thank you so much! looks better than a Gantt

Paul Newcome

Happy to help. 👍️