I have data where the “Frequency” is defined in Columns N as weeks or months, and “last completed date” in Column M. The frequencies are:
1 Week, 2 Weeks, 1 month, 3 months, 6 months, 12 months, 24 months, 36 months, 84 months
I need to add the corresponding frequency to my last completed date. If “months” are being added – than I need the last day of the month. I have the formula as:
=IF(J2="1 week",K2+7,IF(J2="2 weeks",K2+14,IF(J2="1 month",EOMONTH(EDATE(K2,1),0),IF(J2="3 months",EOMONTH(EDATE(K2,3),0),IF(J2="6 months",EOMONTH(EDATE(K2,6),0),IF(J2="12 months",EOMONTH(EDATE(K2,12),0),IF(J2="24 months",EOMONTH(EDATE(K2,24),0),IF(J2="36 months",EOMONTH(EDATE(K2,36),0),if(J2="84 months",eomonth(K2,84),0),if(or(J2="",K2=""),"Frequency or date not known","Entry Error")))))))))
Excel is generating “Too many arguments” error – the formula works if i delete the last 2 if statements (unless i’m coding the if(or) incorrectly)….is there a way around this?
5
Very similar to what Scot and Michal have suggested:
With Excel 2021 and later
=LET(
j2_, J2,
k2_, K2,
j2_count, --LEFT(j2_, FIND(" ", j2_) - 1),
completed_date, IFS(
(j2_ = "") + (k2_ = ""),
"Frequency or date not known",
ISNUMBER(SEARCH("week", j2_)),
k2_ + j2_count * 7,
ISNUMBER(SEARCH("month", j2_)),
EOMONTH(EDATE(k2_, j2_count), 0),
TRUE,
"Entry Error"
),
completed_date
)