Hi all,
Please don't laugh at me for the formula below (it does work!)
Basically I am using a combination of IF & ISNUMBER to lookup if there is a match of a date in various formats, e.g. dd/mm/yyyy or mm/dd/yyyy or mm.dd.yyyy (total of 9 variations possible), in a cell that contains text.
For reference:
- F15 is the country (to determine which date format to pick; e.g. US would be mm/dd/yyyy)
- The Dates_Lookup! table contains the dates in all 9 variations possible (e.g. mm/dd/yyyy or mm.dd.yyyy or mm-dd-yyyy or dd/mm/yyyy...):
All markets with mm/dd/yyyy format (in 3 variations):
* F15=Dates_Lookup!$K$9
* F15=Dates_Lookup!$K$10
* F15=Dates_Lookup!$K$11
All markets with yyyy/mm/dd format (in 3 variations):
* 15=Dates_Lookup!$T$9 to 15=Dates_Lookup!$T$20
All markets that are not following any of the above (see last bit of the formula) it will search for dd/mm/yyyy (in 3 variations)
- RawData!B2 is the cell that contains text in which I am searching in for any of the dates on the Dates_Lookup! table
What I am now trying to achieve is show the value the formula has found instead of "TRUE". E.g. if it found 24.12.2018 it should show 24.12.2018 instead of TRUE. Is that possible somehow?
Many thanks!
Please Login or Register to view this content.
Bookmarks