calculating duration planned Days, Elapsed Days, Remaining and Overrun days

[Abby93]

Greetings
I'm trying to manage a project by calculating its duration in Excel and trying to define( planned Days, Elapsed Days, Remaining and Overrun days automatically with taking into consideration international Calendar and holidays,
Thank you & much appreciated


Hence: in Elapsed Day formula bar, i couldn't add NETWORKDAYS.INTL and the holidays (an error message appears and shows so many arguments is invalid)

 

[Fluff]

Can you post you actual formula.

 

[Abby93]

Work Days Planned
=IF(ISBLANK(A5),"",IF(OR(ISBLANK(C5),ISBLANK(D5)),0,NETWORKDAYS.INTL(C5,D5,7,NK22:NK43))

the above formula worked very well when i added the NETWORKDAYS.INTL and holidays

The problem with Elapsed Days, Remaining and Overrun days when i tried to do the same with the above Work Days Planned (formula)




Work Days Elapsed Formula

=IF(ISBLANK(A6),"",IF(OR(ISBLANK(C6),ISBLANK(D6)),0,IF(Today<C6,0,NETWORKDAYS(C6,IF(ISBLANK(E6),Today-1,E6)))))



Work Days Remain Formula

=IF(ISBLANK(A5),"",IF(OR(ISBLANK(C5),ISBLANK(D5)),0,IF(OR(Today>D5,NOT(ISBLANK(E5))),0,IF(Today<C5,NETWORKDAYS(C5,D5),NETWORKDAYS(Today,D5)))))

Work Days Overrun Formula

=IF(ISBLANK(A6),"",IF(NOT(ISBLANK(E6)),IF(E6>D6,E6-D6,0),0))





holidays

2/22/2022

4/25/2022

4/26/2022

4/27/2022

4/28/2022

5/1/2022

5/2/2022

5/3/2022

5/4/2022

5/5/2022

6/30/2022

7/3/2022

7/4/2022

7/5/2022

7/6/2022

7/7/2022

7/10/2022

7/11/2022

7/12/2022

7/13/2022

7/14/2022

9/23/2022

 

[Fluff]

Thanks for that, how about

=IF(ISBLANK(A6),"",IF(OR(ISBLANK(C6),ISBLANK(D6)),0,IF(Today<C6,0,NETWORKDAYS.INTL(C6,IF(ISBLANK(E6),Today-1,E6),7,NK22:NK43))))

 

[Abby93]

=IF(ISBLANK(A6),"",IF(OR(ISBLANK(C6),ISBLANK(D6)),0,IF(Today<C6,0,NETWORKDAYS.INTL(C6,IF(ISBLANK(E6),Today-1,E6),7,NK22:NK43))))

thank you for that but this formula for which exactly

I'll post the link of the template for your kind reference

Excel Project Plan Template [with project tasks]

Excel Project Plan. Planning a project? Download this free Excel project plan template to kick start your schedule.

www.stakeholdermap.com

i just wanted to change the calendar into international calendar and add the holidays I've done it for the planned days cell successfully, couldn't change it for other cells.

thank you and sorry for any inconvenience.

 

[Fluff]

That is for the elapsed days.​

 

[Abby93]

That is for the elapsed days.​

it worked perfectly for elapsed days but now I'm trying to do it for Remaining date but i couldn't

 

[Fluff]

What is the formula you tried?

 

[Abby93]

What is the formula you tried?

=IF(ISBLANK(A5),"",IF(OR(ISBLANK(C5),ISBLANK(D5)),0,IF(OR(Today>D5,NOT(ISBLANK(E5))),0,IF(Today<C5,NETWORKDAYS.INTL(C5,D5,7,NK22:NK43),NETWORKDAYS.INTL(Today,D5)))))

 

[Fluff]

Try it like

=IF(ISBLANK(A5),"",IF(OR(ISBLANK(C5),ISBLANK(D5)),0,IF(OR(Today>D5,NOT(ISBLANK(E5))),0,IF(Today<C5,NETWORKDAYS.INTL(C5,D5,7,NK22:NK43),NETWORKDAYS.INTL(TODAY(),D5,7,NK22:NK43)))))