DV formula: first day of month with restrictions

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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,226,287
Messages
6,190,059
Members
453,592
Latest member
bcexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top