Combine IF and WORKDAY functions in Gantt

Hi

I need help to plugin the Workday & Holiday formula to my existing formula to calculate the number of days.

=IF(D9="","",IF(F9="",E9-D9+1,(E9-D9+1)-G9))

In above formula:
D9 is the start date
E9 is the End Date
F9 is manual column to calculate the % of work completed and
G9 has the formula to calculate the Work Done [=IF(F9="","",(E9-D9+1)*F9%)]
H9 is the column where Work Left is calculated


What I am looking for is the Work Left (H9) column calculates the total days including weekends. I do not want it to calculate the weekends. Also, I would want that there are certain list of Holidays too which should not be counted too.

Please help! Thanks in advance.

Take a look at the NETWORKDAYS function, and/or pending your local requirements NETWORKDAYS.INTL

If you need more specific guidance, pull together a sample file (inclusive of holiday dates) which houses numerous scenarios of completion - and attach to your next post (via GoAdvanced -> Manage Attachments)

Please find attached the sample sheet I am working on. Its showing 6 days instead it should 4 days. Thanks for your help in advance.

so, using NETWORKDAYS as suggested:

Formula:

Please Login or Register  to view this content.

you can use the above logic and adjust your Conditional Format rule re: GANT accordingly.

Oh, Thanks a ton! This worked. Really appreciate your help and assistance.

Really appreciate your response to it, Thanks a bunch. I am also trying to Colour code the Holidays by Adding new rule, but seems to be giving me an error. Would you please update?

Sure. I have changed the End Date to 8th March. and in my Holidays List 4th March is Holiday and is not getting highlighted.

Bingo! There you go. Thanks a ton for this. Really appreciate all your help and support.