IF OR Fuction

Hello All,

 

Need a help.

A formula to calulate the % duration complete based on IF OR function in excel

 

   Today = (D)28-May-22ItemABDuration% CompleteItem 123-May-2228-May-226B=D,100%, A<D,B>A,(D-A+1)/(B-A+1)Item 225-May-221-Jun-228 



Thanks

Jithin

Planning, Scheduling & Programming Discussion
R
Rafael Davila 👤 Member for 22 years 3 months
· Permalink
  • That activity time has elapsed or that some effort has been performed does not mean some volume of work has been done.
  • Using elapsed time to calculate progress of work is usually wrong even in the most simple of job as things rarely happen exactly as planned.
Z
Zoltan Palffy 👤 Member for 16 years 10 months
· Permalink

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

J
Jithin Kambhikanam 👤 Member for 15 years 2 months
· Permalink

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

R
Rafael Davila 👤 Member for 22 years 3 months
· Permalink

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.


R
Rodel Marasigan 👤 Member for 19 years 7 months
· Permalink

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))

Z
Zoltan Palffy 👤 Member for 16 years 10 months
· Permalink

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)))

Forum Sponsor

Top Posters

Julian Pegg
1 posts
Peter Nagy
2 posts
Raymund de Laza
17 posts
Syed_Asad
0 posts
Tony Greyvenstein
0 posts
Ahmed Al-Jubouri
13 posts
Umar Alvi
3 posts
Sibusiso Mahlalela
0 posts
Michael Samanyayi
3 posts
Simon Gumede
0 posts