SOLVED

Occasional Contributor

I want the date will not take place during vacations, public holidays and weekends.

12 Replies

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.

I tried again and it doesn't work. yes I know its for Saturday and Sunday that's why I tried an easy formula.

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

same problem

best response confirmed by safoine (Occasional Contributor)
Solution

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"

@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 @MindreVetande

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 lists public holidays and vacations

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.

hello @Riny_van_Eekelen

I appreciate your help so much

Can I make the error message depend on data is entered?For example if the day is weekend the error message tell me it's Sunday or Saturday or if its holiday the error message tells that's a holiday.