How to set elegible appoinment dates, skipping weekends and holidays in EXCEL 365

Good day Amin, 

Thank you for sharing your concern.  

Excel 365 can support this scenario by combining a holiday list with built-in working day functions. 

Option 1: Display whether an appointment date is eligible 

Use this method if users enter or select a date and you want Excel to indicate whether that date is available for booking. 

Create a list of national holidays in a separate range, for example H2:H30. 

Enter or select the appointment date in A2. 

In another cell, enter the following formula: 

=IF(NETWORKDAYS(A2,A2,$H$2:$H$30)=1,"Eligible","Not Eligible")

Eligible indicates that the selected date is a valid working day. 

Not Eligible indicates that the date falls on a weekend or matches a holiday in the holiday list. 

This works because the NETWORKDAYS function counts only working days and automatically excludes weekends and any dates listed in the holiday range. 

Option 2: Prevent users from entering invalid appointment dates 

Use this method if you want Excel to reject dates that fall on weekends or holidays. 

Create a list of national holidays in a separate range, for example H2:H30. 

Select the appointment date entry cell, such as A2. 

Open the Data tab and select Data Validation. 

Under the Settings tab: 

Set Allow to Custom. 

Enter the following formula: 

=IF(A2="",TRUE,IFERROR(LET(d,IF(ISNUMBER(A2),INT(A2),DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2)))),NETWORKDAYS(d,d,$H$2:$H$30)=1),FALSE))

Open the Error Alert tab. 

Enter a message such as: "This date is not eligible for appointment booking. Please select a working day that is not a weekend or national holiday." 

Select OK to save the validation rule. 

If a user enters a date that falls on a weekend or holiday, Excel will display the error message and prevent the entry. 

I hope this information is helpful. If you have any questions or need further support, please don’t hesitate to reach out at any time. 

Thank you for your patience and understanding. 

If the answer is helpful, please click "Yes" and kindly upvote it. If you have any extra questions about this answer, please click "Comment".            

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.