% Completed between date ranges

[Fentoei]

Hi All,

Have been sitting here googling and trying to figure this out so I really need your help.

I am creating a tracker spread sheet. It will include Date Received, Deadline, Days Remaining to Deadline ( I have already inputted a formula here). I want to add a column % Completed which will show the progress between the date ranges. I just cannot figure out how to do this.

I have attached the spread sheet I am working on. Also with Column N Days Remaining, how do I leave the cell blank if there are no dates entered in Column H & I? I have left Column M in so show the % Complete.

Many thanks in advance on this

[isskint]

Couple of options;
(TODAY()-[@[Date Received]])/([@Deadline]-[@[Date Received]])
or
1-([@[Days Remaining]]/([@Deadline]-[@[Date Received]]))


for the second part amend your formula to

=if([@[Date Received]]&""="","",[@Deadline]-TODAY()) to just check Date received
or
=if([@[Deadline]]&""="","",[@Deadline]-TODAY()) to just check Deadline
or
IF(OR([@[Date Received]]&""="",[@Deadline]&""=""),"",[@Deadline]-TODAY()) to check if either date

[Fentoei]

Hi isskint

Thank you so much for your help, I really appreciate it.

I am getting error messages in the % completed column where there is no data in the other cells. How do I amend the formula (TODAY()-[@[Date Received]])/([@Deadline]-[@[Date Received]]) to not show errors?

% Complete
33%
#DIV/0!
#DIV/0!
#DIV/0!

Thanks
Fentoei

[kvsrinivasamurthy]

In N3, then drag down

=IF(AND(BidInfo[[#This Row],[Date Received]]<>"",BidInfo[[#This Row],[Deadline]]<>""),BidInfo[[#This Row],[Deadline]]-TODAY(),"")

[isskint]

Apply the same conditional checks supplied for dealing with missing dates for column N. So you need to check if there are any missing dates and tellit what to display. EG

IF([@[Date Received]] & ""="",0,(TODAY()-[@[Date Received]])/([@Deadline]-[@[Date Received]]))) - this will check if Date Recieved is blank and record 0%