| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Validation - Select only Weekend
Is it possible to validate a cell so that only a date which is a saturday or
a sunday can be entered? please help me,its for college coursework thanks sam |
|
#2
|
|||
|
|||
|
If you wanted to install this validation in A1 to A20,
Select A1:A20, then: <Data <Validation Expand the "Allow" box, and choose "Custom". Enter this into the "Formula box: =WEEKDAY(A1,2)5 Then <OK -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Sam" wrote in message ... Is it possible to validate a cell so that only a date which is a saturday or a sunday can be entered? please help me,its for college coursework thanks sam |
|
#3
|
|||
|
|||
|
Data|Validation|Settings tab
Allow: Custom Formula: =WEEKDAY(A1,2)5 Sam wrote: Is it possible to validate a cell so that only a date which is a saturday or a sunday can be entered? please help me,its for college coursework thanks sam -- Dave Peterson |
|
#4
|
|||
|
|||
|
So what would i type as the formula if i wanted the cell to display the full
date (e.g Saturday 19th March) but wanted it to only allow a saturday or sunday, with the date greater or equal to todays date? Thanks Sam "Dave Peterson" wrote: Data|Validation|Settings tab Allow: Custom Formula: =WEEKDAY(A1,2)5 Sam wrote: Is it possible to validate a cell so that only a date which is a saturday or a sunday can be entered? please help me,its for college coursework thanks sam -- Dave Peterson |
|
#5
|
|||
|
|||
|
Try this:
=AND(WEEKDAY(A1,2)5,A1=TODAY()) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Sam" wrote in message ... So what would i type as the formula if i wanted the cell to display the full date (e.g Saturday 19th March) but wanted it to only allow a saturday or sunday, with the date greater or equal to todays date? Thanks Sam "Dave Peterson" wrote: Data|Validation|Settings tab Allow: Custom Formula: =WEEKDAY(A1,2)5 Sam wrote: Is it possible to validate a cell so that only a date which is a saturday or a sunday can be entered? please help me,its for college coursework thanks sam -- Dave Peterson |
|
#6
|
|||
|
|||
|
I'm sorry but i don't understand what the code means. Is it possible for you
to explain what each seperate part of the formula makes the validation do. Many thanks for your help so far. Cheers, Sam "RagDyeR" wrote: Try this: =AND(WEEKDAY(A1,2)5,A1=TODAY()) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Sam" wrote in message ... So what would i type as the formula if i wanted the cell to display the full date (e.g Saturday 19th March) but wanted it to only allow a saturday or sunday, with the date greater or equal to todays date? Thanks Sam "Dave Peterson" wrote: Data|Validation|Settings tab Allow: Custom Formula: =WEEKDAY(A1,2)5 Sam wrote: Is it possible to validate a cell so that only a date which is a saturday or a sunday can be entered? please help me,its for college coursework thanks sam -- Dave Peterson |
|
#7
|
|||
|
|||
|
=and()
means that all the portions within those parentheses must be true for the =And() to be true. =weekday(a1,2) returns a number representing the day of the week. 2 means that Monday is 1 and Sunday is 7. (you can see excel's help for more info) a1=today() just checks to see if the date in A1 is at least as big as today's date. Sam wrote: I'm sorry but i don't understand what the code means. Is it possible for you to explain what each seperate part of the formula makes the validation do. Many thanks for your help so far. Cheers, Sam "RagDyeR" wrote: Try this: =AND(WEEKDAY(A1,2)5,A1=TODAY()) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Sam" wrote in message ... So what would i type as the formula if i wanted the cell to display the full date (e.g Saturday 19th March) but wanted it to only allow a saturday or sunday, with the date greater or equal to todays date? Thanks Sam "Dave Peterson" wrote: Data|Validation|Settings tab Allow: Custom Formula: =WEEKDAY(A1,2)5 Sam wrote: Is it possible to validate a cell so that only a date which is a saturday or a sunday can be entered? please help me,its for college coursework thanks sam -- Dave Peterson -- Dave Peterson |
|
#8
|
|||
|
|||
|
Thanks i now understand what the formula means. But it does not seem to do
the job i am looking for it to do. I need a formula for validation that will only allow certain data to be displayed, and if it doesnt comply it must display an error message. The data must comply with the following terms: Must be a saturday or a sunday Must be a date later than todays date Sorry for the slowness but i am not the most competent person on excel. Thanks for your help, Sam "Dave Peterson" wrote: =and() means that all the portions within those parentheses must be true for the =And() to be true. =weekday(a1,2) returns a number representing the day of the week. 2 means that Monday is 1 and Sunday is 7. (you can see excel's help for more info) a1=today() just checks to see if the date in A1 is at least as big as today's date. Sam wrote: I'm sorry but i don't understand what the code means. Is it possible for you to explain what each seperate part of the formula makes the validation do. Many thanks for your help so far. Cheers, Sam "RagDyeR" wrote: Try this: =AND(WEEKDAY(A1,2)5,A1=TODAY()) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Sam" wrote in message ... So what would i type as the formula if i wanted the cell to display the full date (e.g Saturday 19th March) but wanted it to only allow a saturday or sunday, with the date greater or equal to todays date? Thanks Sam "Dave Peterson" wrote: Data|Validation|Settings tab Allow: Custom Formula: =WEEKDAY(A1,2)5 Sam wrote: Is it possible to validate a cell so that only a date which is a saturday or a sunday can be entered? please help me,its for college coursework thanks sam -- Dave Peterson -- Dave Peterson |
|
#9
|
|||
|
|||
|
If it has to be later than today's date,
=AND(WEEKDAY(A1,2)5,A1=TODAY()) becomes =AND(WEEKDAY(A1,2)5,A1TODAY()) My guess is that you're using RadDyer's formula in a cell that isn't A1. Make sure you change that reference. And on the Error alert tab of the Data|Validation dialog, you can specify your message. Sam wrote: Thanks i now understand what the formula means. But it does not seem to do the job i am looking for it to do. I need a formula for validation that will only allow certain data to be displayed, and if it doesnt comply it must display an error message. The data must comply with the following terms: Must be a saturday or a sunday Must be a date later than todays date Sorry for the slowness but i am not the most competent person on excel. Thanks for your help, Sam "Dave Peterson" wrote: =and() means that all the portions within those parentheses must be true for the =And() to be true. =weekday(a1,2) returns a number representing the day of the week. 2 means that Monday is 1 and Sunday is 7. (you can see excel's help for more info) a1=today() just checks to see if the date in A1 is at least as big as today's date. Sam wrote: I'm sorry but i don't understand what the code means. Is it possible for you to explain what each seperate part of the formula makes the validation do. Many thanks for your help so far. Cheers, Sam "RagDyeR" wrote: Try this: =AND(WEEKDAY(A1,2)5,A1=TODAY()) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Sam" wrote in message ... So what would i type as the formula if i wanted the cell to display the full date (e.g Saturday 19th March) but wanted it to only allow a saturday or sunday, with the date greater or equal to todays date? Thanks Sam "Dave Peterson" wrote: Data|Validation|Settings tab Allow: Custom Formula: =WEEKDAY(A1,2)5 Sam wrote: Is it possible to validate a cell so that only a date which is a saturday or a sunday can be entered? please help me,its for college coursework thanks sam -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
| Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
| Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
| Data Validation Window? | Excel Discussion (Misc queries) | |||
| Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
Linear Mode
