| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date validation
Thanks for reading this post.
In column A I would like to enter dates. For instance the date entered in A5 should be verified for the following. Question :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 could be equal to or greater than A4 Alternate :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 should be equal to TODAY( ) What should be the CUSTOM/FORMULA for both the above validations? Thanks in advance Regards Preman |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date validation
data validation for the cell A5
=IF(A4="",A5<"",IF(A4<"",A54,"")) On Oct 29, 10:57*pm, jpreman wrote: Thanks for reading this post. In column A I would like to enter dates. For instance the date entered in A5 should be verified for the following. Question :- 1 * If A4 is blank, no entry should be permitted in A5 2 * If A4 is not blank, then A5 could be equal to or greater than A4 Alternate :- 1 * If A4 is blank, no entry should be permitted in A5 2 * If A4 is not blank, then A5 should be equal to TODAY( ) What should be the CUSTOM/FORMULA for both the above validations? Thanks in advance Regards Preman |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date validation
For the question:
Uncheck Ignore blank =AND(COUNT(A4),COUNT(A5),A5=A4) For the alternate: =AND(COUNT(A4),A5=TODAY()) -- Biff Microsoft Excel MVP "jpreman" wrote in message ... Thanks for reading this post. In column A I would like to enter dates. For instance the date entered in A5 should be verified for the following. Question :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 could be equal to or greater than A4 Alternate :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 should be equal to TODAY( ) What should be the CUSTOM/FORMULA for both the above validations? Thanks in advance Regards Preman |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date validation
Great !
Thanks a lot Valko. That's exactly what I was looking for. I would be glad if you kindly explain how COUNT function works in your formula. Thanks & regards Preman "T. Valko" wrote: For the question: Uncheck Ignore blank =AND(COUNT(A4),COUNT(A5),A5=A4) For the alternate: =AND(COUNT(A4),A5=TODAY()) -- Biff Microsoft Excel MVP "jpreman" wrote in message ... Thanks for reading this post. In column A I would like to enter dates. For instance the date entered in A5 should be verified for the following. Question :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 could be equal to or greater than A4 Alternate :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 should be equal to TODAY( ) What should be the CUSTOM/FORMULA for both the above validations? Thanks in advance Regards Preman |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date validation
Since you're validating dates the COUNT function makes sure a date is
entered in either cell. In Excel dates are really numbers formatted to look like a date. So, if a date (number) is not entered in A4 then the formulas evaluate to FALSE and the validation will not permit an entry into A5. -- Biff Microsoft Excel MVP "jpreman" wrote in message ... Great ! Thanks a lot Valko. That's exactly what I was looking for. I would be glad if you kindly explain how COUNT function works in your formula. Thanks & regards Preman "T. Valko" wrote: For the question: Uncheck Ignore blank =AND(COUNT(A4),COUNT(A5),A5=A4) For the alternate: =AND(COUNT(A4),A5=TODAY()) -- Biff Microsoft Excel MVP "jpreman" wrote in message ... Thanks for reading this post. In column A I would like to enter dates. For instance the date entered in A5 should be verified for the following. Question :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 could be equal to or greater than A4 Alternate :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 should be equal to TODAY( ) What should be the CUSTOM/FORMULA for both the above validations? Thanks in advance Regards Preman |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date validation
Got it.
Once again many thanks to you. Kind regards Preman "T. Valko" wrote: Since you're validating dates the COUNT function makes sure a date is entered in either cell. In Excel dates are really numbers formatted to look like a date. So, if a date (number) is not entered in A4 then the formulas evaluate to FALSE and the validation will not permit an entry into A5. -- Biff Microsoft Excel MVP "jpreman" wrote in message ... Great ! Thanks a lot Valko. That's exactly what I was looking for. I would be glad if you kindly explain how COUNT function works in your formula. Thanks & regards Preman "T. Valko" wrote: For the question: Uncheck Ignore blank =AND(COUNT(A4),COUNT(A5),A5=A4) For the alternate: =AND(COUNT(A4),A5=TODAY()) -- Biff Microsoft Excel MVP "jpreman" wrote in message ... Thanks for reading this post. In column A I would like to enter dates. For instance the date entered in A5 should be verified for the following. Question :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 could be equal to or greater than A4 Alternate :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 should be equal to TODAY( ) What should be the CUSTOM/FORMULA for both the above validations? Thanks in advance Regards Preman |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date validation
Thanks for responding to my post Madhu. The formula did not satisfy condition 1. Perhaps you can take have a look and correct it. Regards Preman "muddan madhu" wrote: data validation for the cell A5 =IF(A4="",A5<"",IF(A4<"",A54,"")) On Oct 29, 10:57 pm, jpreman wrote: Thanks for reading this post. In column A I would like to enter dates. For instance the date entered in A5 should be verified for the following. Question :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 could be equal to or greater than A4 Alternate :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 should be equal to TODAY( ) What should be the CUSTOM/FORMULA for both the above validations? Thanks in advance Regards Preman |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date validation
You're welcome!
-- Biff Microsoft Excel MVP "jpreman" wrote in message ... Got it. Once again many thanks to you. Kind regards Preman "T. Valko" wrote: Since you're validating dates the COUNT function makes sure a date is entered in either cell. In Excel dates are really numbers formatted to look like a date. So, if a date (number) is not entered in A4 then the formulas evaluate to FALSE and the validation will not permit an entry into A5. -- Biff Microsoft Excel MVP "jpreman" wrote in message ... Great ! Thanks a lot Valko. That's exactly what I was looking for. I would be glad if you kindly explain how COUNT function works in your formula. Thanks & regards Preman "T. Valko" wrote: For the question: Uncheck Ignore blank =AND(COUNT(A4),COUNT(A5),A5=A4) For the alternate: =AND(COUNT(A4),A5=TODAY()) -- Biff Microsoft Excel MVP "jpreman" wrote in message ... Thanks for reading this post. In column A I would like to enter dates. For instance the date entered in A5 should be verified for the following. Question :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 could be equal to or greater than A4 Alternate :- 1 If A4 is blank, no entry should be permitted in A5 2 If A4 is not blank, then A5 should be equal to TODAY( ) What should be the CUSTOM/FORMULA for both the above validations? Thanks in advance Regards Preman |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| DATE VALIDATION | Excel Discussion (Misc queries) | |||
| date validation | Excel Worksheet Functions | |||
| Date Validation | Excel Worksheet Functions | |||
| Date Validation - Must equal Sundays date | Excel Discussion (Misc queries) | |||
| Date validation | Excel Discussion (Misc queries) | |||
Linear Mode
