Log In
Register
Home
Active Member
February 17, 2023
Offline
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.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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.
VIP
Trusted Members
December 7, 2016
Offline
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
Active Member
February 17, 2023
Offline
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.
1 Guest(s)
