Splitting dates into 3 cells each and with data validation rules

MarkieVBA

New Member
Joined
Nov 4, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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,
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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

New Member
Joined
Nov 4, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Sorry - Replace the ? in the formula with a /

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

MarkieVBA

New Member
Joined
Nov 4, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

VBA Code:
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,734

ADVERTISEMENT

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


Then if an incorrect date is selected:

1605792743445.png


With userforms you have total control and flexibility
 

MarkieVBA

New Member
Joined
Nov 4, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,850
Messages
5,574,650
Members
412,607
Latest member
caner
Top