Nov 12 2020 07:02 AM
I want the date will not take place during vacations, public holidays and weekends.
Nov 12 2020 07:20 AM
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.
Nov 12 2020 07:44 AM
I tried again and it doesn't work. yes I know its for Saturday and Sunday that's why I tried an easy formula.
Nov 12 2020 07:44 AM
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
Nov 12 2020 07:53 AM
SolutionNov 12 2020 08:04 AM - edited Nov 12 2020 08:06 AM
@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.
Nov 12 2020 08:34 AM
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
Nov 12 2020 08:35 AM
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.
Nov 12 2020 09:55 AM
@safoine Perhaps best demonstrated in the attached file. See if you can "translate" the data validation to your own workbook.
Nov 12 2020 10:57 AM
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.
Nov 12 2020 11:07 AM
@safoine In the Input message you could stat that the date should not be a weekend or a holiday. Then in the error message you can write a more friendly message explaining what input is accepted, but I don't think it's possible to create an error message depending on the particular reason for the error (i.e. Saturday, Sunday or Holiday). But perhaps I'm wrong. Don't know.
Nov 12 2020 07:53 AM
Solution