Date formula that functions like WORKDAY but includes weekends

1. Date formula that functions like WORKDAY but includes weekends

Is there a formula similar to WORKDAY that would include weekends and make something due on the next business day? For example, I have a bill due on 6/1/11 and I need to follow up 5 days later - which would be normally 6/6/11 but the WORKDAY formula makes it 6/8/11 as it is adding 2 extra days. Any help would be greatly appreciated, as this is a project for work.

I am using Excel 2010.

Thank you!

2. Re: Date formula that functions like WORKDAY but includes weekends

I assume you still want to consider holidays? If not, then simply adding 5 to the originial date will do the trick.

=A1 + 5

3. Re: Date formula that functions like WORKDAY but includes weekends

=C2+D2+SUMPRODUCT(--(C2<\$A\$2:\$A\$4),--(C2+D2>\$A\$2:\$A\$4))

This assumes your holidays are in A2:A4:, your start date is in C2 and your number of days to add is in D2

See the attached for an example.

4. Re: Date formula that functions like WORKDAY but includes weekends

Originally Posted by Whizbang
=C2+D2+SUMPRODUCT(--(C2<\$A\$2:\$A\$4),--(C2+D2>\$A\$2:\$A\$4))

This assumes your holidays are in A2:A4:, your start date is in C2 and your number of days to add is in D2

See the attached for an example.
thank you sooooooo much! It worked perfectly!

5. Re: Date formula that functions like WORKDAY but includes weekends

Originally Posted by Whizbang
I assume you still want to consider holidays? If not, then simply adding 5 to the originial date will do the trick.

=A1 + 5
I have to exclude holidays & dates we are closed otherwise this would have worked, but thank you for the help.

6. Re: Date formula that functions like WORKDAY but includes weekends

Originally Posted by Whizbang
=C2+D2+SUMPRODUCT(--(C2<\$A\$2:\$A\$4),--(C2+D2>\$A\$2:\$A\$4))
I don't think this will work in all circumstances. In the example with 60 days for example, adding another holiday, e.g. on 15/04/2011 should make the end date move back 1.

I believe you need an array formula like this

=C2+SMALL(IF(COUNTIF(holidays,C2+ROW(INDIRECT("1:"&D2+COUNT(holidays)))),"",ROW(INDIRECT("1:"&D2 +COUNT(holidays)))),D2)

confirmed with CTRL+SHIFT+ENTER

where holidays is a named range containing holiday dates

7. Re: Date formula that functions like WORKDAY but includes weekends

Hi

With a defined range to include any holiday dates, wouldn't the following achieve what you want.

=A1+5+COUNTIF(Holidays,A1+5)

8. Re: Date formula that functions like WORKDAY but includes weekends

I'm not sure how that's supposed to work, Roger.

If A1 has 23rd December 2011 and 25th and 26th December 2011 are included in the holiday range then shouldn't the result be 30th December 2011? Your formula will return just A1+5 = 28th December 2011. My suggested array formula will return 30th Dec as expected

I note also that 1gambit says he is using Excel 2010 (although 2003 is shown in profile) so with WORKDAY.INTL function from 2010 should be able to use

=WORKDAY.INTL(C2,D2,"0000000",holidays)

[untested]

9. Re: Date formula that functions like WORKDAY but includes weekends

Hi

Quite right, ddl, works fine with Intl version of Workday.
My proposal was only testing if 5 days after the date was a holiday.

10. Re: Date formula that functions like WORKDAY but includes weekends

yes this formula works.
Is there any method to exclude only Sundays (without listing the sundays in holidays name range). I use Excel 2007 and therefore workday.intl is not available. Please also tell that whether any add-in available for workday.intl usage in excel 2007.

11. Re: Date formula that functions like WORKDAY but includes weekends

This version adds workdays excluding Sundays only

=A1-WEEKDAY(A1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1,3))))

assuming start date in A1 and days to add in B1

That doesn't exclude holidays, though, do you need that?

12. Re: Date formula that functions like WORKDAY but includes weekends

Yes i need to exclude sundays and holidays both

13. Re: Date formula that functions like WORKDAY but includes weekends

Yes please I need to exclude both holidays and sundays

This version adds workdays excluding Sundays only

=A1-WEEKDAY(A1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1,3))))

assuming start date in A1 and days to add in B1

That doesn't exclude holidays, though, do you need that?

TIA

16. Re: Date formula that functions like WORKDAY but includes weekends

you can use this, where F contains yoru holidays, and G contains a list of sundays.

=A2+B2+COUNTIFS(F:F,"<="&A2+B2,F:F,">="&A2)+COUNTIFS(G:G,"<="&A2+B2,G:G,">="&A2)

17. Re: Date formula that functions like WORKDAY but includes weekends

Thanks
For this one has to write all sundays in G:G to work this. But if i had to do this i can achieve it with the formula in post #6 of this thread, by including all sundays in 'holidays' itself.

18. Re: Date formula that functions like WORKDAY but includes weekends

thanks Wizbang, you're a genius!

19. Re: Date formula that functions like WORKDAY but includes weekends

I know I am bumping an old post (3+ years), but in searching I am trying to do similar here as well

Is it possible to compute similar to the WorkDay function but include all weekends, both Saturday and Sundays, but no holidays?

There are currently 1 users browsing this thread. (0 members and 1 guests)