Tom Urtis
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 11,305
Help from the formula wizards please...
I have a date in cell Y6.
I need a data validation formula for cell Z6.
In cell Z6, I want to validate the entry for being a date that is the first day of the month and not less than the first day of the month and year of the date value in cell Y6.
Details:
The date in Y6 could be any date, examples
October 24, 2003
June 1, 1999
March 30, 2007
Cell Z6 requires a date, that the date be the first day of whatever month and year of the date value is being entered, and that said date is not prior to the first day of the month and year of the date in cell Y6.
Given the above examples, if Y6 contains the date October 24, 2003, then cell Z6 can accept a date of October 1, 2003 (because that is still within the calendar month and year of October 24, 2003), or November 1, 2003 (because it is in the future based on October 24, 2003), or May 1, 2008 (because it is in the future based on October 24, 2003).
Keeping with the example of October 24, 2003 being in cell Y6, what would NOT be OK for entry in cell Z6 is
October 2, 2003
or
November 15, 2004
or
May 24, 2004
because they are not the first day of the month date values.
What would also NOT be OK for cell Z6 is
September 1, 2003
Or
February 1, 2003
because they are dates of a calendar month and year prior to the date in cell Y6.
If cell Y6 is empty, any date is allowable; data validation need not care about an empty Y6.
Three more restrictions:
- cannot involve VBA (as you can imagine, not my choice)
- cannot involve analysis toolpak functions
- cannot involve add-ins or utilities of any kind; must be native Excel functions for a data validation formula.
Thanks in advance if anyone can think up a solution for this one.
I have a date in cell Y6.
I need a data validation formula for cell Z6.
In cell Z6, I want to validate the entry for being a date that is the first day of the month and not less than the first day of the month and year of the date value in cell Y6.
Details:
The date in Y6 could be any date, examples
October 24, 2003
June 1, 1999
March 30, 2007
Cell Z6 requires a date, that the date be the first day of whatever month and year of the date value is being entered, and that said date is not prior to the first day of the month and year of the date in cell Y6.
Given the above examples, if Y6 contains the date October 24, 2003, then cell Z6 can accept a date of October 1, 2003 (because that is still within the calendar month and year of October 24, 2003), or November 1, 2003 (because it is in the future based on October 24, 2003), or May 1, 2008 (because it is in the future based on October 24, 2003).
Keeping with the example of October 24, 2003 being in cell Y6, what would NOT be OK for entry in cell Z6 is
October 2, 2003
or
November 15, 2004
or
May 24, 2004
because they are not the first day of the month date values.
What would also NOT be OK for cell Z6 is
September 1, 2003
Or
February 1, 2003
because they are dates of a calendar month and year prior to the date in cell Y6.
If cell Y6 is empty, any date is allowable; data validation need not care about an empty Y6.
Three more restrictions:
- cannot involve VBA (as you can imagine, not my choice)
- cannot involve analysis toolpak functions
- cannot involve add-ins or utilities of any kind; must be native Excel functions for a data validation formula.
Thanks in advance if anyone can think up a solution for this one.