Ask Your Question
0

How to update cells based on what other cell contains?

asked 2019-12-17 17:01:57 +0200

LearningLibre gravatar image

updated 2019-12-17 17:04:18 +0200

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.

edit retag flag offensive close merge delete

Comments

Please attach the sample file.

m.a.riosv gravatar imagem.a.riosv ( 2019-12-17 21:47:29 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-12-17 22:03:58 +0200

Lupp gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-12-17 17:01:57 +0200

Seen: 60 times

Last updated: Dec 17 '19