conditional formatting data bar for Due Date

[DDT~123]

Greetings All,

I'm needing a little help with the formula for creating a data bar which compares the date a task was assigned to the due date. Column A has the "Assigned Date", Cell B1 has the TODAY() formula, and Column C has the "Due Date". The progress bar would need to compare the 3 dates and convert to a percentage of time remaining. If the Due Date is equal to TODAY then it would need to show 100% and the data bar completely full.

Thank you in advance for your help!!

 

[AlexandraT]

If what you want is time remaining try this: =((C1-TODAY())/(C1-A1)) and format it as percentage.
However, if time remaining is what you want, when due date equals to today then time remaining would be 0% and not 100%. If you still want it to be 100% even so, add an if statement: =IF(C1=TODAY(),1,(C1-TODAY())/(C1-A1)). You can replace TODAY() with your cell B1 of course.
For conditional formatting, try here: How to Create Progress Bars in Excel With Conditional Formatting but use 0 and 1 instead of 0 and 100 for Min and Max. Conditional formatting also has the option of not showing the number just the bar if you prefer as well.

Alex

 

[DDT~123]

Thank you for replying Alex! I input your formula but it outputs "100%" if the value in Column A equals 6/19/15 and the value in Column C equals 7/1/15.

 

[AlexandraT]

Today is 6/19/15 so time remaining is all the time allocated in the first place, meaning 100% of the time.

If, on the other hand, you want to know how much time has passed, try this: =IF(C1=TODAY(),1,1-(C1-TODAY())/(C1-A1))

Alex