Restricting Specific Dates in any Given month

[paxile2k]

This may be a two part question, I finally was able to build a calender contol to insert dates into a active cell, now what i am trying to do is be able to restrict the usage of certain dates. For instance, dates prior to the current date cannot be chosen, nor dates that fall on 29th, 30th and month depending the 31st. Is this possible? Its is being used to calculate amounts for certain days, for example the calender object places a date in Cell A1, which falls between 1st-28th, another column also has a date option, the date to be chosen cannot be before todays date, and cannot be after the 28th of any given month. Is there a way to restrict in the calender or would a if statement apply? OR would a more simple approach to use data validation? where as (A1=MM, B1=DD, C1=YY) so that D1=A1/B1/C1 ie. 07/22/09, so that when using D1 it would be a date format. which would be the start date and E1,F1,G1 for a new date, then use conditional formatting to restrict or prevent choosing dates before TODAY() for the second date colums, I know a message could be displayed for it if it is less then/prior to the current date, or should i have the data validation adjust (if at all possible)for the first set of date options?

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

[paxile2k]

Thankyou for your response, i modified my original quesion to include more details and options

[shg]

Thank you. But it's your title that you need to change.

[paxile2k]

I appologize, I have changed it, Thankyou again for your response

[shg]

That is infinitely better, thank you.

I don't know if it will work with the calendar control -- you should try it.

You could combine that with conditional formatting, so if one of the earlier dates is changes, it highlights an invalid date in other cells.

If the validation doesn't work, it would require VBA -- although the conditional formatting alone might provide enough of cue to the user.

[paxile2k]

Thankyou for your response,
If i am using the Data Validation Option, is there a way to convert 3 cells to a date in the 4th cell, ex A1=MM, B1=DD, C1=YEAR, so D1 will display a date with A1/B1/C1 so i can use D1 for calculations and what not. With the Data Validation, i can restrict the days after the 28th which did solve one on my question and ultimately led me to discarding a calender to restrict dates.

[paxile2k]

Found the Solution, A very big duh on my part. The formula =Date(cell1,cell2,cell3) fixes the date conversion for mutliple cells Thankyou again for your help

[shg]

Why enter m, d, and y in three separate cells? If A1 contains a date, then =DAY(A1) returns the day (1-31) and =MONTH(A1) returns the month.

[paxile2k]

That is true, it is mostly to use for adjusting a due date, so a current due date is A1=07 B1=20 C1=2009, (If there was a way to eliminate the year, that would be a good thing as apposed to having another column and just have the current year append in the =date(a1,b1,(current year)) function, and based on how months are, a new due date cannot fall after the 28th, so the a1+b1+c1=07/20/09 in D1, then another bunch of columns has a different date range. The ulitmate goal is to calculate a payment from the first date, to the new date, which i know how to calculate dates between dates, thats the easy part. Its just getting it all together was the issue, it was a bit confusing for me.

[martindwilson]

well the data validation would be
=AND(A1>=today(),DAY(A1)<>29,DAY(A1)<>30,DAY(A1)<>31)
but it depends on how the date is put in cell
oh well while i was puzzling that out problem allready solved!

[paxile2k]

I solved the issue with Data Validation, by using a list option for the 12 months and a drop down for the 28 days in any given month, then by using the =Date(YEAR(NOW()),B1,A1) to get the due date, then a if statement using <Today() to meet conditions. which if it is less then today it will display a message to reselect a different date. Thankyou all for your help. I was greatly appreciated