• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

multiple Date validation using VBA

A

abdulncr

Member
Hi Friends,


Can any one help me on this. multips date validateion in a cell.if there is only one date to validate, i should have used Data-validation-date-between.


I have in the sheet2 leaveplan from date in the C5:C100 and to date in the D5:D100

In the another Sheet1 'from date in the A5:A50': and 'to date in the B5:B50'

When i enter the date in the sheet2, it should not allow to enter the date if it is falling between date mention in the sheet1.


Regards

Abdul
 
Hi Abdul ,


Let us assume your date is entered in a cell named Entered_Date ; your range of FROM dates is a named range From_Dates , and your range of TO dates is named To_Dates. Either of the following formulae can be used as Data Validation :


=ISERROR(MATCH(1,(Entered_Date>=From_Dates)*(Entered_Date<=To_Dates),0))


=SUMPRODUCT((Entered_Date>=From_Dates)*(Entered_Date<=To_Dates))=0


Use either of the above in your Custom Data Validation.


Narayan
 
Hi,


Thanks for your help.


First i named the range accordingly to work the formula

Sumproduct shows error "the formula currently evaluate error u want to continuer"


when i tried match index, it accepts. but not restricting any thing. it allows to enter any date


Regards

Abdul
 
Hi,


I Named cell as per the formula, it is same as you posted.


=ISERROR(MATCH(1,(Entered_Date>=From_Dates)*(Entered_Date<=To_Dates),0))


it is not restricting any thing.


Regards

Abdul
 
Hi


I have put From Date in B1 and To Date in B2.


The data validation is applied in D1 as follows,


=AND(D1>=$B$1,D1<=$B$2)


Amritansh
 
Hi Abdul ,


The full procedure is as follows :


1. Select your range of from dates , say Sheet1 A5:A50 ; select Formulas , Name Manager , New , and type in the name From_Dates.


2. Select your range of to dates , say Sheet1 B5:B50 ; select Formulas , Name Manager , New , and type in the name To_Dates.


3. Select your first range of date entry , say Sheet2 C5:C50 ; select Data Validation , Custom , and in the formula box , put the following formula :


=ISERROR(MATCH(1,(C5>=From_Dates)*(C5<=To_Dates),0))


Click OK.


Now try to enter various dates in the range C5:C50 and see whether invalid dates are accepted.


Repeat step 3 for the other column of date entry , say Sheet2 D5:D50.


Narayan
 
Dear Narayan,

Thanks for your time to help me. I did excactly you told. still it is not validating any date. meanwhile i got a VBA code to do this validation.


Thank you for your help


Regards

Abdul
 
You must log in or register to reply here.
Back
Top