Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting to exclude weekend and Bank Holidays | Excel Worksheet Functions | |||
Conditional formatting to exclude weekend and Bank Holidays | Excel Worksheet Functions | |||
Conditional formatting to exclude weekend and Bank Holidays | Excel Worksheet Functions | |||
Due date excluding weekend days | Excel Worksheet Functions | |||
How to Find Weekend date | Excel Worksheet Functions |