Date + 14 days. The date is generated by a regular expression

I have a speadsheet in which the first column is like

Column 1
-------------------------
Foo 1234 (22.09.21)
Baaaar 5678 (20.10.21)

And a second column contains dates only and is generated from the first one by the =REGEX() function:

=REGEX($A2,"^.+\(([[:digit:]|\.]+)\)$","$1")

Column 2
-------------------------
22.09.21
20.10.21

And I need a third column, which should contain values generated as date_in_column_2 + 14_days.

What I have I tried is =B2+14 in C2, but if the date in B2 is generated by a regex instead of typed by hand, =B2+14 in C2 gives me an error.

How should I fix it? Thanks.

Also, there is another question. I know it is against Q&A rules and I can ask it as a separate question, but in case it is really easy, could one show how to perform date_in_column_2 + 14_days calculation for working days only, i.e. without Saturdays and Sundays?

Solution 1:

You need to convert the string result of your regex into a (numeric) date value first before applying calculations on that value.

LibreOffice provides the DATEVALUE() function to convert the date string, so you need to embed your formula in Column B into that function:

=DATEVALUE(REGEX($A2,"^.+(([[:digit:]|.]+))$","$1"))

To get the result displayed as date instead of an numeric value, you need to format column 2 appropriately (Date format).

To add 14 working days, LibreOffice provides the WORKDAY() function. Thus, put it into Column C with "B2" as first argument and "14" as second one: