I need a help with counting days formula!

[Colinahn]

Hi,

I'm trying to calculate the arriving date that automatically skips weekend and need a help.

For example,

If the start date is 12/09, and it takes 10days to arrive,

the arriving date will be 12/19 which is Saturday.

Since Saturday is a weekend, I want it to be displayed as 12/21 which is Monday. 

I tried using WORKDAYS.INTL, but the problem was that it did not count weekend during the shipping period.

What I wanted to do is to automatically avoid showing weekend when it's arriving.

What function should I use to make this work?

[#2]

@Colinahn Hi

Try below formula, hope it will help

=IF(WEEKDAY((A2+B2),2)=6,(A2+B2)+2,IF(WEEKDAY((A2+B2),2)=7,(A2+B2)+1,A2+B2))

A sample file is also attached for your reference.

Please let me know if it works for you.

Thanks

Tauqeer

[#3]

@tauqeeracma 

Hi, Thank you for the reply!

It works great! but if there is TBA schedule, I would like to describe it as "TBD".

How can I modify your formula that can also show TBD if there is no date confirmed?

Would IFERROR work?

[#4 · Accepted solution]

@Colinahn 

I have modified the formula, please check and let me know if it works as needed.

Thanks

Tauqeer

[#5]

@tauqeeracma 

Thank you so much!

But, I have another problem.

I also need to avoid certain holidays.

Is there any way I can revise and set certain days as holidays and set a formula that can automatically jump to a weekday if it's a holiday?

[#6]

@Colinahn You are welcome

There is one formula in excel NETWORKDAYS() that can be helpful in this scenario, but I would suggest you to please provide some sample data (few examples with holidays) then more specific solution can be shared.

Thanks

Tauqeer

[#7]

I AM NEW TO THIS. I have the same question about how to connect the networkdays to my formulas . For instance Im a school and i need to only count school days not weekends or national holidays