Multiple conditions to identify errors on a Date field

[thanhng34]

Hi everyone,

I want to create a flag (FlagErrorDate) to identify errors in the "Date of birth" field. The rule is as follows:

If Date (Date of birth) is not between '01' and '31'
or Month (Date of birth) is not between '01' and '12'
or Year (Date of birth) <= 1900 -> 'Error', otherwise 'OK'

So I wrote the following expression but it didn't work :

If (not match (Date (Date of birth), '01', '02', '03', ....., '31)) or not match (Mont (Date of birth), '01', ' 02 ',' 03 ', .....,' 12 ') or Year (Date of birth) <= 1900,' Error ',' OK 'as FlagErrorDate

Can someone help me write the right expression? Is there another method of doing this without having to list all the values to compare: 01, 02, 03, .... 31

Thank you very much in advance

[Vegar]

I think you are missing brackets around Date of birth. Like below.

If (not match (Date ([Date of birth] ), '01', '02', '03', ....., '31)) or not match (Mont ([Date of birth]), '01', ' 02 ',' 03 ', .....,' 12 ') or Year ([Date of birth]) <= 1900,' Error ',' OK 'as FlagErrorDate

[thanhng34]

Hi Vegar,

This expression didn't work. I have 'Error' on all cells of the new field "FlagErrorDate".

This is what I wrote :

if(not match (Date("Date of birth"),'01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31') or not match(Month("Date of birth"), '01','02','03','04','05','06','07','08','09','10','11','12')
or year("Date of birth")<=1900, 'Error', 'OK') as FlagErrorDate

[Vegar]

Oh I think I see what's causing this issue 🙂  Date("Date of birth") will return a date not the day of month. Try to use  Day("Date of birth") instead.

if(not match (Day("Date of Birth"), '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31') or not match(Month("Date of birth"), '01','02','03','04','05','06','07','08','09','10','11','12')
or year("Date of birth")<=1900, 'Error', 'OK') as FlagErrorDate