ValidIF Challenge

[Haus]

I'm sure there's a simple solution to this that's just escaping me at the moment. In my form, I have a Date of Birth column. The challenge is that my users are sometimes going too fast and entering either today's date by mistake or a date that would result in the person being 1, 2, or even negative years old. I currently have a ValidIf in place (see below) preventing them from entering an unsupported date. 

AND([_THIS]>'01/01/1800',[_THIS]<='01/01/2200')

This expression works as intended, but does not prevent the user from entering today's date or even a future date. I would like to modify this to prevent the user from entering a date that would result in the person being less than 10 years old and an accidental date in the future.

Thank you in advance for your assistance!!  

[Suvrutt_Gurjar · Accepted solution]

---

@Haus wrote:

I would like to modify this to prevent the user from entering a date that would result in the person being less than 10 years old and an accidental date in the future.

---

You could try a valid if expression something like 

AND(
[_THIS]<EOMONTH(TODAY(), -121)+DAY(TODAY()),
[_THIS]>EOMONTH(TODAY(), -1801)+DAY(TODAY())
)

This will ensure the dates are older than 10 years from today and higher than 150 years (1873)  in the past  from today So the current band of valid_if is 1873-2013 when date is entered in the year 2023.

You could adjust the band of earliest date possible in the past by adjusting the digit 1801 in the above expression. If you wish it to be 100 years in teh past (1923 onwards to 2013), please try 100*12+1=1201, for 70 years in the past , please try 70*12+1 = 841 (1953-2013)  and so on in place of 1801 above.

If you do not wish any restriction on how old the date can be in the past as long as it is more than 10 years in the past  from today, please try 

[_THIS]<EOMONTH(TODAY(), -121)+DAY(TODAY())

Please test well.

[Suvrutt_Gurjar · Accepted solution]

---

@Haus wrote:

I would like to modify this to prevent the user from entering a date that would result in the person being less than 10 years old and an accidental date in the future.

---

You could try a valid if expression something like 

AND(
[_THIS]<EOMONTH(TODAY(), -121)+DAY(TODAY()),
[_THIS]>EOMONTH(TODAY(), -1801)+DAY(TODAY())
)

This will ensure the dates are older than 10 years from today and higher than 150 years (1873)  in the past  from today So the current band of valid_if is 1873-2013 when date is entered in the year 2023.

You could adjust the band of earliest date possible in the past by adjusting the digit 1801 in the above expression. If you wish it to be 100 years in teh past (1923 onwards to 2013), please try 100*12+1=1201, for 70 years in the past , please try 70*12+1 = 841 (1953-2013)  and so on in place of 1801 above.

If you do not wish any restriction on how old the date can be in the past as long as it is more than 10 years in the past  from today, please try 

[_THIS]<EOMONTH(TODAY(), -121)+DAY(TODAY())

Please test well.

[diogolupcosta · Accepted solution]

[_THIS] < (TODAY() - 3652)