Forum Discussion
please help
I want the date will not take place during vacations, public holidays and weekends.
- 1. Are you absolutely sure your computers language setting is US/English? In some other languages we use ; instead off, as separator
2. In some cases WEEKday starts from 0 instead of 1. Try the Weekday(C3) outside of "data validation"
12 Replies
safoine Perhaps your local settings require a semi-colon to separate arguments in formulae.
Try =AND(WEEKDAY(A2)<>1;WEEKDAY(A2)<>7)
Oooops: didn't see the previous post until I posted mine.
Hello Riny_van_Eekelen
thank you a lot it's working
do you have any formula to make the date will not take place during vacations, public holidays and weekends please ?
i create two liste public holidays and vacations.
safoine Perhaps best demonstrated in the attached file. See if you can "translate" the data validation to your own workbook.
saturday/sunday
=C3=(WORKDAY(C3-1,1))
=C3=(WORKDAY.INTL(C3-1,1))
With holidays and vacations in a list on the sheet:
MyHolidaySheet!$A$1:$A$17
=C3=(WORKDAY(C3-1,1,MyHolidaySheet!$A$1:$A$17))
=C3=(WORKDAY.INTL(C3-1,1,,MyHolidaySheet!$A$1:$A$17))
I'm not sure about the difference between WORKDAY and WORKDAY.INTL.
WORKDAY might pick up "ordinary" bank-holidays in a US system. It does not with my regional settings.
Try
Hello safoine,
That's interesting. I have entered the same formula and am not receiving an error. Perhaps you could open a new worksheet and enter the formula again?
Note that this data validation allows only dates that are not Saturday's and Sunday's. It does not account for vacations and holidays.
- 1. Are you absolutely sure your computers language setting is US/English? In some other languages we use ; instead off, as separator
2. In some cases WEEKday starts from 0 instead of 1. Try the Weekday(C3) outside of "data validation"
