Excel percentage between todays date and due date

Question

So i need to complete a dashboard and then turn the percentage results into a chart, to see where we are in a Yearly Plan. Lets say the column/the task is "i have to renovate the bedroom", i need a column for the closing date (when i do it) and a row for the due date (lets say 30.06.2023). I need the percentage to calculate how much time i have left until the due date. But if the task is done before the due date, the percentage should be 100%. This should calculate as if daily the percentage is increasing as if i take daily actions, and i get closer to the 100% approaching the due date. I am stuck for 2 h trying different formulas, nothing seems to work. Help would be highly appreciated!

I tried everything i could that i knew of. But nothing seems to calculate between todays date and the due date.

Answer 1

I found a solution for you.

first, build a table like this:

then, in the column "ClosingDate" you will write:

="X"

next, in the column "TimePass%" you will write:

=IF([@ClosingDate]<=[@DueDate],1,IF([@ClosingDate]="X",1-(DAYS([@DueDate],NOW())/DAYS([@DueDate],[@StartingDate]))))

then:

• when you didn't finish the task the table automatically write "X" and the it will calculate the time passed in %.
• when you finished task earlier it will write 100%.

hope that helps.

the dates in here are in this format: DD/MM/YYYY same will work for other formats.