Forum Discussion
searching for dates to trigger an action.
There are several challenges here. Getting the dates to be extracted from the text is one of them.
The file attached has one possible solution for that.
The bigger problem, though, is that the dates you want to work with are before 1990 and the Excel date numbering system starts on January 1, 1990. Any date before that is not recognized by Excel natively.
I did not work on a solution for that, but I found this article that may be a good starting point.
https://exceluser.com/1057/how-to-work-with-dates-before-1900-in-excel/
Power Query recognizes dates before 1990 and can handle the filtering aspect very easily.
Two challenges there: Power Query functionality in Excel for Mac is limited, and when you load your data into Excel you would have to convert those dates to text. But if all you care about is to find those dates, you can do that in Power Query and then have the results loaded as text into Excel.
I hope this helps.
- The First Date in Excel is the 01.01.1900 Not 01.01.1990!!!!
- Correct! My typo above. Sorry for the confusion.
- Thank you.That is good to know but the dates I need to look for are (<= 1875) and (>= 1899).
jhicks5charternet
If you only want to search a value in your Text >=1875 an <=1895 you can use this formula in your conditional formating rule:
=SUM(IFERROR(FIND(SEQUENCE(21;;1875);B2);0)) < maybe you have to repace semicolon with comma
- Celia, For various reasons I lost sight of this project and am now getting back to it. I know nothing of Power Query but when I try to get to it I am told that I must go to settings and download the ODBC Driver Manager and set up the database driver. I do not know where to find this . Its it actually another program? Since I am on Office 365 and a Mac, I am not sure sometimes that the directions apply to me or I am just not understanding. John Hicks
- Sorry, I cannot help with Excel for Mac.
