I am stuck trying to convert this dynamic formula to use the CONCATENATE function instead of the &s. I am using an Excel add-in that does not accept "&" in formulas. I have tried several times to do this myself but cannot figure out what I am doing wrong. It doesn't seem to play nicely with the EOMONTH Function. This formula is used to show the number of days within each period whether it be monthly, quarterly or yearly.

=IF(AND(D7="MONTHLY",ISNUMBER(SEARCH("FORECAST",D6))),DAYS(EOMONTH(LEFT(D8,3)&" 1, "&RIGHT(D6,4),0),EOMONTH(LEFT(D8,3)&" 1, "&RIGHT(D6,4),-1)),IF(AND(D7="MONTHLY",ISNUMBER(SEARCH("ACTUAL",D6))),DAYS(EOMONTH(LEFT(D8,3)&" 1, "&LEFT(D6,4),0),EOMONTH(LEFT(D8,3)&" 1, "&LEFT(D6,4),-1)),IF(D7="Quarter",DAYS(EOMONTH(TEXT(DATE(RIGHT(D6,4),LEFT(D8,1)*3,1),"MMM")&" 1, "&RIGHT(D6,4),0),EOMONTH(TEXT(DATE(RIGHT(D6,4),LEFT(D8,1)*3,1),"MMM")&" 1, "&RIGHT(D6,4),-3)),IF(D7="Cumulated",DAYS(EOMONTH("Dec 31, "&RIGHT(D6,4),0),EOMONTH("Dec 31, "&RIGHT(D6,4),-12)),""))))

Here is a sample of the the text that these formulas are using.

Monthly column example

D6=Forecast 2018

D7=Monthly

D8= January

Quarterly column example

Q6= Forecast 2019

Q7= Quarter

Q8=1st Quarter

Yearly column example

V6=Forecast 2020

V7= Cumulated

V8 = December

=IF(AND(D7="MONTHLY",ISNUMBER(SEARCH("FORECAST",D6))),DAYS(EOMONTH(LEFT(D8,3)&" 1, "&RIGHT(D6,4),0),EOMONTH(LEFT(D8,3)&" 1, "&RIGHT(D6,4),-1)),IF(AND(D7="MONTHLY",ISNUMBER(SEARCH("ACTUAL",D6))),DAYS(EOMONTH(LEFT(D8,3)&" 1, "&LEFT(D6,4),0),EOMONTH(LEFT(D8,3)&" 1, "&LEFT(D6,4),-1)),IF(D7="Quarter",DAYS(EOMONTH(TEXT(DATE(RIGHT(D6,4),LEFT(D8,1)*3,1),"MMM")&" 1, "&RIGHT(D6,4),0),EOMONTH(TEXT(DATE(RIGHT(D6,4),LEFT(D8,1)*3,1),"MMM")&" 1, "&RIGHT(D6,4),-3)),IF(D7="Cumulated",DAYS(EOMONTH("Dec 31, "&RIGHT(D6,4),0),EOMONTH("Dec 31, "&RIGHT(D6,4),-12)),""))))

Here is a sample of the the text that these formulas are using.

Monthly column example

D6=Forecast 2018

D7=Monthly

D8= January

Quarterly column example

Q6= Forecast 2019

Q7= Quarter

Q8=1st Quarter

Yearly column example

V6=Forecast 2020

V7= Cumulated

V8 = December

Last edited: