Forum Discussion
searching for dates to trigger an action.
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 Colum...
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
- 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
- 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? 😉