searching for dates to trigger an action.

Copper Contributor

I am using a Mac with Microsoft 365.  I have another question regarding using Excel.

My spreadsheet involves three columns and many rows.  I am trying to design a formula to allow me to search Column B for any Year date between 1875 and 1895.  My B column looks like the following :

 
22 Mar 1882 - East Randolph, Cattaraugus, New York, USA
9 August 1931 - East Randolph, Cattaraugus County, New York, USA
 

I want to search all rows in Column B to determine if they contain a year date between 1875 and 1895.  If it does contain such a date I want to either conditionally format that row or move it to a different area of the sheet.  Can this be done?  I have tried IF, AND, FIND etc but I can't find a combo that will do this.  AS yet there has been success. I am not sure if the dates read as text so I can't use the >or< to find the proper dates.  John

 

 
19 Replies

@jhicks5charternet 

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.

 

 

 

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
The First Date in Excel is the 01.01.1900 Not 01.01.1990!!!!
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

German_Chris_0-1625594722425.png

 

Correct! My typo above. Sorry for the confusion.
Sorry, I cannot help with Excel for Mac.
Chris, I Tried using The above formula after substitution of the "," and it worked somewhat. It did not pick the proper dates. In fact I can't figure out why it picked what it did, /Users/john/Desktop/Screen Shot 2021-07-07 at 1.36.38 PM (2).png
John
@jhicksScharternet
I think you want to show a screenshot, but it doesn`t show here. Please try again.
Can you show me the formula in your conditional formating please.
Please try to change the B2 in the formula to B1.
Wow, That did straighten the results. Though it still is missing some of the dates, it does pick up most of them. In a small sample I found 1896,1897,1898,1899 Not including in the highlighted. Is that a factor of the 21 in the formula? My brain is having difficulty understanding how the functions you used allow for this result. I understand conditional formatting but would never have picked those functions to try. Thank you, John
Hi,
I‘m happy That I could help.
Yes, the formula checks values from 1875 to 1895 (pls See your first Post) . If you wanz to check until 1899 change the 21 to 24, that should do it.
Sequenzen function gives you an Array of numbers-> {1875, 1876, … , 1899}
Find function check If one of this numbers is in the cell value and gives you the position of an error .
Iferror changes the errors to 0
SUM adds all positions in one cell.
If the sum is> 0 the conditional formating interprets this as True :)

Easy isn’t ist? ;)
It is not so easy for this old mind, but its is getting through slowly with the help of people like you. Thank you again. John

@jhicks5charternet 

Unsurprisingly, the best solution depends on the tools available.  Given access to regular expressions (here I use Charles Williams's Fast Excel) the first 4-digit string, giving the year, may be extracted for each row and presented as a conditional format icon.

 

image.png

Alternatively, the condition may be used in a FILTER formula. 

Avoiding the use of add-ins one could then have

 

image.png

Peter, Thank you for this information. These formulas look interesting but I do not know enough to know where to enter them. Could you elaborate more on the basics of how to enter these and were to enter these? Thanks John Hicks

@jhicks5charternet 

Hi John

Not so mysterious really; they are 'ordinary' worksheet dynamic array formulas.  Ordinary for me that is, others may not agree.  The starting point is the defined name 'entry' that I applied to your data entry column, containing a list of dates and addresses.  The function rgx.MID comes from a paid add-in so is shown for information but is of little relevance unless you have access to the add-in.

 

The formula contained in cell C3 generates 1 or 0 as a dynamic array depending on whether the date lies within the specified period or not.  This is taken a step further in cell F3 when the function FILTER is applied to remove entries that do not lie within the specified period.