Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error message needed if incorrect Date Of Birth (DOB) entered
Hi,
In cell D6 I have a DOB entered....in cell E6 I have a datedif function that has 3 conditional formatting set as: 1 - If D6 is blank, E6 is blank. 2 - If DOB in D6 is between 18 - 24 then E6 shows age and cell is yellow. 3 - If DOB in D6 is 25 or over then E6 shows age and cell is blue. What I need is when a DOB is entered in D6 under 16 years old, then D6 returns an error message -" You have Entered an Age under 16 Years - Retry!! ". I have tried Data Validation, but cant get a message from tha as I dont know what to set as the date validation. I do need any 16 or 17 year olds to show as they are included in my customer age range so they can stay white background. If there is vba code for this then more than happy to try that as am slowly trying to lean as much as I can Any ideas?? Thanks in anticipation My datedif formula is: =IF(D6="","",DATEDIF(D6,TODAY(),"Y")) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error message needed if incorrect Date Of Birth (DOB) entered
On 26 Jan., 22:37, Craig wrote:
Hi, In cell D6 I have a DOB entered....in cell E6 I have a datedif function that has 3 *conditional formatting set as: 1 - If D6 is blank, E6 is blank. 2 - If DOB in D6 is between 18 - 24 then E6 shows age and cell is yellow. 3 - If DOB in D6 is 25 or over then E6 shows age and cell is blue. What I need is when a DOB is entered in D6 under 16 years old, then D6 returns an error message -" You have Entered an Age under 16 Years - Retry!! ". I have tried Data Validation, but cant get a message from tha as I dont know what to set as the date validation. I do need any 16 or 17 year olds to show as they are included in my customer age range so they can stay white background. If there is vba code for this then more than happy to try that as am slowly trying to lean as much as I can Any ideas?? Thanks in anticipation My datedif formula is: =IF(D6="","",DATEDIF(D6,TODAY(),"Y")) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error message needed if incorrect Date Of Birth (DOB) entered
Sorry, hit the wrong key...
Select D6 and goto Data Validation Allow: Custom Formula: =DATEDIF (D6,TODAY(),"Y")=16 Set up your error message in the 'Error Alert' tab. Regards, Per On 27 Jan., 00:23, Per Jessen wrote: On 26 Jan., 22:37, Craig wrote: Hi, In cell D6 I have a DOB entered....in cell E6 I have a datedif function that has 3 *conditional formatting set as: 1 - If D6 is blank, E6 is blank. 2 - If DOB in D6 is between 18 - 24 then E6 shows age and cell is yellow. 3 - If DOB in D6 is 25 or over then E6 shows age and cell is blue. What I need is when a DOB is entered in D6 under 16 years old, then D6 returns an error message -" You have Entered an Age under 16 Years - Retry!! ". I have tried Data Validation, but cant get a message from tha as I dont know what to set as the date validation. I do need any 16 or 17 year olds to show as they are included in my customer age range so they can stay white background. If there is vba code for this then more than happy to try that as am slowly trying to lean as much as I can Any ideas?? Thanks in anticipation My datedif formula is: =IF(D6="","",DATEDIF(D6,TODAY(),"Y"))- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error message needed if incorrect Date Of Birth (DOB) entered
Thanks Per,
Did as instructed but get the error message when any DOB entered...did try changing the in the formula to < and back again - no luck. I copied and cross checked your formula to ensure I hadnt made an error! Any ideas Thanks "Per Jessen" wrote: Sorry, hit the wrong key... Select D6 and goto Data Validation Allow: Custom Formula: =DATEDIF (D6,TODAY(),"Y")=16 Set up your error message in the 'Error Alert' tab. Regards, Per On 27 Jan., 00:23, Per Jessen wrote: On 26 Jan., 22:37, Craig wrote: Hi, In cell D6 I have a DOB entered....in cell E6 I have a datedif function that has 3 conditional formatting set as: 1 - If D6 is blank, E6 is blank. 2 - If DOB in D6 is between 18 - 24 then E6 shows age and cell is yellow. 3 - If DOB in D6 is 25 or over then E6 shows age and cell is blue. What I need is when a DOB is entered in D6 under 16 years old, then D6 returns an error message -" You have Entered an Age under 16 Years - Retry!! ". I have tried Data Validation, but cant get a message from tha as I dont know what to set as the date validation. I do need any 16 or 17 year olds to show as they are included in my customer age range so they can stay white background. If there is vba code for this then more than happy to try that as am slowly trying to lean as much as I can Any ideas?? Thanks in anticipation My datedif formula is: =IF(D6="","",DATEDIF(D6,TODAY(),"Y"))- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error message needed if incorrect Date Of Birth (DOB) entered
The formula is correct and works for me. Are you sure that you've entered a
valid Excel date, and not text? -- David Biddulph "Craig" wrote in message ... Thanks Per, Did as instructed but get the error message when any DOB entered...did try changing the in the formula to < and back again - no luck. I copied and cross checked your formula to ensure I hadnt made an error! Any ideas Thanks "Per Jessen" wrote: Sorry, hit the wrong key... Select D6 and goto Data Validation Allow: Custom Formula: =DATEDIF (D6,TODAY(),"Y")=16 Set up your error message in the 'Error Alert' tab. Regards, Per On 27 Jan., 00:23, Per Jessen wrote: On 26 Jan., 22:37, Craig wrote: Hi, In cell D6 I have a DOB entered....in cell E6 I have a datedif function that has 3 conditional formatting set as: 1 - If D6 is blank, E6 is blank. 2 - If DOB in D6 is between 18 - 24 then E6 shows age and cell is yellow. 3 - If DOB in D6 is 25 or over then E6 shows age and cell is blue. What I need is when a DOB is entered in D6 under 16 years old, then D6 returns an error message -" You have Entered an Age under 16 Years - Retry!! ". I have tried Data Validation, but cant get a message from tha as I dont know what to set as the date validation. I do need any 16 or 17 year olds to show as they are included in my customer age range so they can stay white background. If there is vba code for this then more than happy to try that as am slowly trying to lean as much as I can Any ideas?? Thanks in anticipation My datedif formula is: =IF(D6="","",DATEDIF(D6,TODAY(),"Y"))- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error message needed if incorrect Date Of Birth (DOB) entered
David & Per,
My apologies.....I attempted the validation before I left for work this morning - mistake....got into work and tried again and perfect....Boss happy and looking for more stuff - dont they always !!!! thank you Per and thank you David for taking time to read post Craig "David Biddulph" wrote: The formula is correct and works for me. Are you sure that you've entered a valid Excel date, and not text? -- David Biddulph "Craig" wrote in message ... Thanks Per, Did as instructed but get the error message when any DOB entered...did try changing the in the formula to < and back again - no luck. I copied and cross checked your formula to ensure I hadnt made an error! Any ideas Thanks "Per Jessen" wrote: Sorry, hit the wrong key... Select D6 and goto Data Validation Allow: Custom Formula: =DATEDIF (D6,TODAY(),"Y")=16 Set up your error message in the 'Error Alert' tab. Regards, Per On 27 Jan., 00:23, Per Jessen wrote: On 26 Jan., 22:37, Craig wrote: Hi, In cell D6 I have a DOB entered....in cell E6 I have a datedif function that has 3 conditional formatting set as: 1 - If D6 is blank, E6 is blank. 2 - If DOB in D6 is between 18 - 24 then E6 shows age and cell is yellow. 3 - If DOB in D6 is 25 or over then E6 shows age and cell is blue. What I need is when a DOB is entered in D6 under 16 years old, then D6 returns an error message -" You have Entered an Age under 16 Years - Retry!! ". I have tried Data Validation, but cant get a message from tha as I dont know what to set as the date validation. I do need any 16 or 17 year olds to show as they are included in my customer age range so they can stay white background. If there is vba code for this then more than happy to try that as am slowly trying to lean as much as I can Any ideas?? Thanks in anticipation My datedif formula is: =IF(D6="","",DATEDIF(D6,TODAY(),"Y"))- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create error message if number entered is greater than previous ce | Excel Discussion (Misc queries) | |||
#VALUE! error message help needed please | Excel Worksheet Functions | |||
How to give an error message if a cell value entered is larger than permitted | Excel Discussion (Misc queries) | |||
Inserting current date when number entered in cell & Circular reference error? | Excel Discussion (Misc queries) | |||
formula to calculate age using birth date and current date | Excel Worksheet Functions |