I want to add specific amount of time to a date.

I have a date (german formatting/ dd.mm.yy) in C2. Then I have a duration in D2, which is either in years or month. (1 year, 2 years or 1 month, 2 months, ...)

In G2 I want the date from C2 the value from D2, and on top the month rounded up to the 1st of the following month.

I already tried a nested IF function =IF(FIND("month";D2); EDATE(DATE((RIGHT(C2;2));MID(C2;4;2) (LEFT(D2;FIND(" ";D2)-1));"01");1); IF(FIND("year";D2);EDATE(DATE((RIGHT(C2;2) (LEFT(D2;FIND(" ";D2)-1)));MID(C2;4;2);"01");1);""))

But that only works if there are X month(s) in D2. If there are X year(s) then I get #VALUE! and an error that tells me that "FIND" cannot find "month" in "2 years".

CodePudding user response：

Try this in G2:

=lambda(splitdate;addtime;
lambda(month;year;addamnt;addunit;
date(100 year (regexmatch(addunit;"year")*addamnt);month (regexmatch(addunit;"month")*addamnt) 1;1))(
index(splitdate;;2);index(splitdate;;3);index(addtime;;1);index(addtime;;2)))(
split(C2;".");split(D2," "))

N.B. 1: The output is in dd/mm/yyyy format; use a custom number format to convert this back into dd/mm/yy for display. N.B. 2: I've assumed that you want to round the result to the first day of the next month even if the date in D2 is the first day of a month; it's a fairly simple modification if this isn't what you want to do.