Invalid date if weekend or bank holiday
Hi,
I have a spreadsheet where cell C6 requires the user to enter a date, I currently have a custom validation in the cell of the following:- =WEEKDAY(C6,2)<=5 This displays an error message if a non-working day (i.e. a Saturday or Sunday) is entered in the cell. Is there anyway I could get this to work if the user also enters a bank holiday date. Regards, Glenn |
Invalid date if weekend or bank holiday
In a separate area in your workbook enter all bank holidays. Select
this range and InsertNameDefine... call it Holidays. Your DV formula: =AND(WEEKDAY(C6,2)<=5,COUNTIF(Holidays,C6)=0) HTH Kostis Vezerides On Jun 14, 3:25 pm, grobertson wrote: Hi, I have a spreadsheet where cell C6 requires the user to enter a date, I currently have a custom validation in the cell of the following:- =WEEKDAY(C6,2)<=5 This displays an error message if a non-working day (i.e. a Saturday or Sunday) is entered in the cell. Is there anyway I could get this to work if the user also enters a bank holiday date. Regards, Glenn |
Invalid date if weekend or bank holiday
I have tried this but it is giving me the following error message :-
"A named range you specified cannot be found" "vezerid" wrote: In a separate area in your workbook enter all bank holidays. Select this range and InsertNameDefine... call it Holidays. Your DV formula: =AND(WEEKDAY(C6,2)<=5,COUNTIF(Holidays,C6)=0) HTH Kostis Vezerides On Jun 14, 3:25 pm, grobertson wrote: Hi, I have a spreadsheet where cell C6 requires the user to enter a date, I currently have a custom validation in the cell of the following:- =WEEKDAY(C6,2)<=5 This displays an error message if a non-working day (i.e. a Saturday or Sunday) is entered in the cell. Is there anyway I could get this to work if the user also enters a bank holiday date. Regards, Glenn |
Invalid date if weekend or bank holiday
try networkday()
you may have to install the analysis toolpack =networkday(date in question,date in question, hol list)=0 will give a true when a holoday or weekend date is selected. "grobertson" wrote: Hi, I have a spreadsheet where cell C6 requires the user to enter a date, I currently have a custom validation in the cell of the following:- =WEEKDAY(C6,2)<=5 This displays an error message if a non-working day (i.e. a Saturday or Sunday) is entered in the cell. Is there anyway I could get this to work if the user also enters a bank holiday date. Regards, Glenn |
Invalid date if weekend or bank holiday
I still can't get this to work.
I just want it to not validate if a date that I enter is a wekend or bank holiday "bj" wrote: try networkday() you may have to install the analysis toolpack =networkday(date in question,date in question, hol list)=0 will give a true when a holoday or weekend date is selected. "grobertson" wrote: Hi, I have a spreadsheet where cell C6 requires the user to enter a date, I currently have a custom validation in the cell of the following:- =WEEKDAY(C6,2)<=5 This displays an error message if a non-working day (i.e. a Saturday or Sunday) is entered in the cell. Is there anyway I could get this to work if the user also enters a bank holiday date. Regards, Glenn |
Invalid date if weekend or bank holiday
Post your formula
"grobertson" wrote: I still can't get this to work. I just want it to not validate if a date that I enter is a wekend or bank holiday "bj" wrote: try networkday() you may have to install the analysis toolpack =networkday(date in question,date in question, hol list)=0 will give a true when a holoday or weekend date is selected. "grobertson" wrote: Hi, I have a spreadsheet where cell C6 requires the user to enter a date, I currently have a custom validation in the cell of the following:- =WEEKDAY(C6,2)<=5 This displays an error message if a non-working day (i.e. a Saturday or Sunday) is entered in the cell. Is there anyway I could get this to work if the user also enters a bank holiday date. Regards, Glenn |
Invalid date if weekend or bank holiday
set up a range with the holidays you are interested in (Hol) and use in your
validation equations =and(WEEKDAY(C6,2)<=5,c6<hol) "grobertson" wrote: I still can't get this to work. I just want it to not validate if a date that I enter is a wekend or bank holiday "bj" wrote: try networkday() you may have to install the analysis toolpack =networkday(date in question,date in question, hol list)=0 will give a true when a holoday or weekend date is selected. "grobertson" wrote: Hi, I have a spreadsheet where cell C6 requires the user to enter a date, I currently have a custom validation in the cell of the following:- =WEEKDAY(C6,2)<=5 This displays an error message if a non-working day (i.e. a Saturday or Sunday) is entered in the cell. Is there anyway I could get this to work if the user also enters a bank holiday date. Regards, Glenn |
Invalid date if weekend or bank holiday
Come to think of it, I occasssionally have problems with named ranges.
if you do , try to just put in the actual rewange where you have the holidays listed. (in my test it was H1:H3) "grobertson" wrote: I still can't get this to work. I just want it to not validate if a date that I enter is a wekend or bank holiday "bj" wrote: try networkday() you may have to install the analysis toolpack =networkday(date in question,date in question, hol list)=0 will give a true when a holoday or weekend date is selected. "grobertson" wrote: Hi, I have a spreadsheet where cell C6 requires the user to enter a date, I currently have a custom validation in the cell of the following:- =WEEKDAY(C6,2)<=5 This displays an error message if a non-working day (i.e. a Saturday or Sunday) is entered in the cell. Is there anyway I could get this to work if the user also enters a bank holiday date. Regards, Glenn |
All times are GMT +1. The time now is 03:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com