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,
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,