I have a data on cells A1 to A8 given as in text format. How can I update the cells I1 to I8 respectively, namely that if A1 is either “1.1.2019”, “22.4.2019”, “30.5.2019” or “2.11.2019”, then I1 should have the value “special day” and otherwise empty? And similarly I2 shows if A2 is a special day and so on.
Please attach the sample file.
Assuming “update” should read “by formula” (under Autocalc):
Your main problem is the usage of an outdated and distorted date format. Even in the Europen countries where dot-separated dates are used due to tradition, the day and the month should always be given with two digits.
The only textual representation of dates actually usable beyond stubborn traditionas is described by the format string YYYY-MM-DD. This format is also convertible to the numeric representation of dates in spreadsheets by the standard function DATEVALUE(). Numeric dates then can be displayed in any format (but should only be in ISO 8601). First of all they are unambiguously comparable.
Therefore you need a formula for converting M.D.YYYY dates to numeric. You can use the REGEX() function coming with LibO Calc V 6.2 and higher as a kind of format converter first. For the string in A2 e.g.:
=DATE(REGEX(A2;"(?<=\.)\d+$";;1);REGEX(A2;"(?<=\.)\d+(?=\.)";;1);REGEX(A2;"\d+(?=\.)";;1))
Well, RegEx are a topic of its own.
Having converted your dates in an unambiguous representation you can use the MATCH() function for testing if a specific date is occurring in a range.
See this attached example.