Splitting dates into 3 cells each and with data validation rules

[MarkieVBA]

Hi,

I have two pairs of dates on a form. These dates are split into their 3 components and can't be merged into one for entry purposes.
The form user can leave the day fields blank if only the month is known. The first date must always be before the second.
The 6 cells are formatted as Custom '00'.

So the first date is day, month, year - A1 B1 C1
The second date is day, month, year - A2 B2 C2

This works well for the user.

But for my needs (and for excel to understand it) I reintegrate these dates each back to a hidden and protected single cell for calculations in another part of the form (formatted as Date dd/mm/yyyy) as:

D1 is =IF(OR(ISBLANK(C1),ISBLANK(B1)),"",DATE(20&C1,B1,IF(ISBLANK(A1),1,A1)))
D2 is =IF(OR(ISBLANK(C2),ISBLANK(B2)),"",DATE(20&C2,B1,IF(ISBLANK(A1),1,A1)))

If the day is missing, each is assumed as the 1st of the month which is what I need to make it possible to calculate a financial period for instance.

So far so good, but I need to prevent the user entering out of range or wrong way round dates for those other 6 cells. So far I've tried Data Validation:

Each is set to Allow Custom, Ignore blank, with error message "Start Date cannot be after Finish Date"

For A1 - Formula =AND(D1<=D2,A1>0,A1<32)
For B1 - Formula =AND(D1<=D2,B1>0,B1<13)
For C1 - Formula =AND(D1<=D2,C1>18,C1<99)

And for the second date:
Each is set to Allow Custom, Ignore blank, with error message "Finish Date cannot be before Start Date"

For A2 - Formula =AND(D1<=D2,A2>0,A2<32).
For B2 - Formula =AND(D1<=D2,B2>0,B2<13)
For C2 - Formula =AND(D1<=D2,C2>18,C2<99)

This is supposed to prevent the user entering a second date larger than the first while also restricting the days to more than 0 or less than 32, months more than 0 and less than 13, and years over 18 but less than 99.

However, this only works sometimes. The rules can be circumvented intermittently especially if entered non-sequentially.
Worse still, it allows for dates like 31st November and 31st February which mess up the date re-integration in the D1 and D2 cells giving a date in the following month.

Is there any way I can enforce these rules without breaking the split form design?

Many thanks in advance,

 

[sijpie]

Instead of D1 containing the formula you have, which will accept illogical day or month numbers, use DATEVALUE. This will show an error when incorrect numbers are provided. You can then have another cell (E1 for instance) show the errormessage if D1 has an error:

D1: =IF(OR(ISBLANK(C1),ISBLANK(B1)),"",DATEVALUE(IF(ISBLANK(A1),1,A1)&"/"&B1&"?"&20&C1)
E1: =ISERROR(D1,"","The days or months entered do not make sense. Please correct")

Will that help?

If you work with macros you can make it more fancy, disabling buttons to continue while the error is active

 

[MarkieVBA]

Instead of D1 containing the formula you have, which will accept illogical day or month numbers, use DATEVALUE. This will show an error when incorrect numbers are provided. You can then have another cell (E1 for instance) show the errormessage if D1 has an error:

D1: =IF(OR(ISBLANK(C1),ISBLANK(B1)),"",DATEVALUE(IF(ISBLANK(A1),1,A1)&"/"&B1&"?"&20&C1)
E1: =ISERROR(D1,"","The days or months entered do not make sense. Please correct")

Will that help?

If you work with macros you can make it more fancy, disabling buttons to continue while the error is active

Thanks but I'm getting #VALUE on D1 when entering a valid date.

 

[sijpie]

Sorry - Replace the ? in the formula with a /

The formula is for dd/mm/yy dateformat. If different in your region, then rearrange

 

[MarkieVBA]

Sorry - Replace the ? in the formula with a /

The formula is for dd/mm/yy dateformat. If different in your region, then rearrange

Thanks it works perfectly now and is much cleaner than the code I was using.

I discovered E1 should be:
=IF(ISERROR(D1),"The days or months entered do not make sense. Please correct","")

But applying the message to a cell isn't practical for my form as there isn't much room. It also doesn't prevent the user from entering bad dates.

I've noticed that Data Validation seems to work only once, then allows errors. I even tried:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("D1") > Range("D2") Then
        MsgBox "Start Date cannot be after Finish Date"
    End If
    
    If Range("D2") < Range("D1") Then
        MsgBox "Finish Date cannot be before Start Date"
    End If

End Sub

.. but this also allows bad dates.

I also applied validation to D1 and D2 direct to set a between range, but no matter what cells I placed the rule in, Excel ignored it.

 

[sijpie]

If you work with macros anyway, then why not use a little userform to get the correct date?
You could have a date picker pop up when the user selects the date cell, or if you prefer typed input have a little userform with the three input fields. The OK button stays grey until a valid date has been used.

is that something you could see working? Of course you can have your whole form that you now have in the sheet transferred into a userform, with all the advantages of that (error checking, help information).

 

[sijpie]

It would look something like this if the user clicks on D2

Then if an incorrect date is selected:

With userforms you have total control and flexibility

 

[MarkieVBA]

Thanks but the form is already way too heavily designed to be changed (the ABC cell refs are not the real locations). I've not had much luck getting my head around the UserForm designer and just use standard sheets with rows and columns set to a fine grid size.

However, after exhaustive research (and trial and error), I discovered this appears to be working:

Private Sub Worksheet_Change(ByVal Target As Range)
DateError
If Range("D1").Value > Range("D2").Value Then
    MsgBox "Finish Date cannot be before Start Date"
    Range("A2:C2").ClearContents
    Range("A2").Select
    Exit Sub
End If
End Sub

Private Sub DateError()
If Range("E1") = 1 Then
Range("A1:C1").ClearContents
End
ElseIf Range("E2") = 1 Then
Range("A2:C2").ClearContents
Exit Sub
End If
End Sub

I used your formulas in D1 and D2:
=IF(OR(ISBLANK(C1),ISBLANK(B1)),"",DATEVALUE(IF(ISBLANK(A1),1,A1)&"/"&B1&"/"&20&C1))
=IF(OR(ISBLANK(C2),ISBLANK(B2)),"",DATEVALUE(IF(ISBLANK(A2),1,A2)&"/"&B2&"/"&20&C2))

and modified your message cell E1 and E2 as 'helper' cells
=IF(ISERROR(D1),1,0)
=IF(ISERROR(D2),1,0)

The helper cells were needed as it was a monumental headache trying to work out syntax problems with ElseIf ISerror, EndIf block and 1004 errors with the change range not wanting to deal with a #VALUE! directly.

Now the data is cleared with false entries including the 2nd line if lesser than the 1st even if adjusted in reverse.

 

[sijpie]

Nice work. Thanks for posting your solution