Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Expression to search for dates

Hi everyone...   I am trying to tweak an expression, that searches a column for a year value, to account for the fact that the years 2021 and 2022 have been brought in to the source data.   The e...
Need Help
  • zaza's avatar
    zaza
    6 years ago

    You might have a problem with your original expression as "*20" will match "2019" as well since 2019 contains the number 20.

     

    The SEARCH function returns the starting position of the substring within the string. So you will get a match if the value starts at the position 1. But since you use a wildcard in the front, it will always get a match if the number 20 is contained within your string. So even 10/20/1999 will get matched.

     

    Try this instead:

     
    MATCH =
IF ( SEARCH ( "2019", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2019",
IF ( SEARCH ( "2020", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2020",
IF ( SEARCH ( "2021", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2021",
IF ( SEARCH ( "2022", 'TEST'[DATESTRING], 1, 0 ) <> 0, "2022"
) ) ) )

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous try this

 

Year = IF(SEARCH("*19",'Table'[column],1,0)>0,"2019",IF(SEARCH("*20",'Table'[column],1,0)>0,"2020",IF(SEARCH("*21",'Table'[column],1,0)>0,"2021","2022")))

 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you parry2k . It's still only working for 2019 and 2020 though. What purpose does the ">0" serve?