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:

Column 2

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?

I used semicolons instead of commas.
Cell format as date.
The RegEx function generates a text not a value, so you first have to transform the text into a value, then you may add another value (date).

Better convert the (German) date form to a distinct ISO 8601 date form with a 4-digits year, of which taking the value works in any locale (and any 2-digits year setting), not just German and a few others.


and then use DATEVALUE() instead of VALUE() that does not attempt to convert almost anything that somewhat resembles a number string (in case the data does not contain an expected date), so

1 Like