I have the following formula: =IF(U$1<$E14,0,IF(U$1>$F14,1,(U$1-$E14)/($F14-$E14))) which works when there are dates in all the cells, however, when cell F14 is blank it returns 100%. When F14 is blank I want it to return the % complete based on the date in cell U1 - I know I probably need another IF statement in there but can't get it to work.
I find it difficult to visualise the issue. Can you please attach a file with dates and describe what each of these represent? I can make some assumptions myself, like that U1 contains the current date, E14 the start date and F14 the end date. But, I prefer that you confirm or correct these assumptions.
Hello,
Based on your formula, the section I have highlighted with red text, I can only assume that the date in U1 is greater than the date in F14, thus giving you 100%, especially so when F14 is empty.
=IF(U$1<$E14,0,IF(U$1>$F14,1,(U$1-$E14)/($F14-$E14)))
You need to handle the scenario when F14 is empty.
Br,
Anders
The attached file shows an example of the issue, but you have an actual start and finish dates and a month date. Some times there won't be a finish date as the work is ongoing, but I would like to show current % complete. Hope that makes sense.
I can't access your file for some reason but, if there is no finish date, how do you calculate the total duration to work out a percentage?
I would suggest you base the duration using the current data if there is nothing else
