(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?
(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.
Hello All,
Need a help.
A formula to calulate the % duration complete based on IF OR function in excel
| Today = (D) | 28-May-22 | |||
| Item | A | B | Duration | % Complete |
| Item 1 | 23-May-22 | 28-May-22 | 6 | B=D,100%, A<D,B>A,(D-A+1)/(B-A+1) |
| Item 2 | 25-May-22 | 1-Jun-22 | 8 |
Thanks
Jithin
Printer-friendly version
Send to friend
glad I could help but my formula was correct based on what you had
you had A<D
NOT A>D or B<D
was not excalty sure I but I did think your paramaters were incorrect to begin with but that was not what you asked
glad I could help wants exactly sure what you were trying to do.
Thanks Zoltan and Rodel,
That did help.
Rafel,
The weightage per activity is already given based on the resource requirement and its 7X7 job. So the duration consumed X by the weightage/ activity gives me the planned %.
Thanks
Jithin
And what about non-work days such as vacations?
And what about multiple resouces with different calendars?
And what about shiftwork on same activity?
Unless activities are duration type such as concrete curing status and updating is a matter of volume of work. Statusing and updating activities that perform volume of work without considering volume of work is not a good idea.
Zoltan furmula is correct with minor correction in case A > D or B < D or A=D.
=IF(B3<=$D$2,1,IF(A3<=$D$2,($D$2-A3+1)/(B3-A3+1),0))
Based on what you had here it is
| Item | Duration % Complete | Today = (D) | |
| A | B | C | 28-May-22 |
| 23-May-22 | 28-May-22 | 100% | |
| 25-May-22 | 1-Jun-22 | 50% |
=IF(B3=$D$2,1,IF(A3<$D$2,($D$2-A3+1)/(B3-A3+1)))
Email: PPadmin@planningplanet.com
Replies