Formula to display "October/November"

[ChrisVella]

I have a sheet with 7 columns, one for each day of the week showing only the day value. I also have a cell which shows the month in text format example “October”. I wish that in those weeks which overlap between two months, the value of this cell changes to “October/November”.

I managed to write a formula that determines if there’s overlap or not =IF(MONTH(C2)=MONTH(O2),C2,1) where C2 is Monday and O2 is Sunday. I am unable to set the value to the combination of the two (with what should I replace the 1 above?).

[rautamiekka]

I keep researching. and this escalated into a rather big problem. No wonder you’re asking this question.

[Lupp]

C2 containing the starting date of your week:

=TEXT(C2;"MMMM") & IF(MONTH(C2) = MONTH(C2+6); ""; " /  " & TEXT(C2+6;"MMMM"))

Better: Put the FormatCode (according with your locale) into A1 and the delimiter (above " / ") into A2. Then:

=TEXT(C2;A1) & IF(MONTH(C2) = MONTH(C2+6); ""; A2 & TEXT(C2+6;A1)).

[rautamiekka]

In my testing this code works with the spreadsheet I reverse-engineered although I can’t make promises it’ll work later:

=IF(
    MONTH(C2)==MONTH(O2);

    TEXT(
        NOW();
        "mmmm"
    ) & "/" & TEXT(
        NOW()+O2;
        "mmmm"
    );

    1
)

No newlines:

=IF(MONTH(C2)==MONTH(O2); TEXT(NOW(); "mmmm") & "/" & TEXT(NOW()+O2; "mmmm"); 1)

The file: This and next month in a cell with overlapping days, rautamiekka 0.ods

[ChrisVella]

That would return the present month and not the month of the date in C2 or O2. Also, if there’s overlap the formula would return “1”.

[rautamiekka]

@Chris, I know, I had no clue what else to put there.

[ChrisVella]

I found a solution myself with the following formula:

=IF(MONTH(C2)=MONTH(O2),TEXT(DATE(YEAR(C2),MONTH(C2),DAY(C2)),"mmmm"),CONCATENATE(TEXT(DATE(YEAR(C2),MONTH(C2),DAY(C2)),"mmmm"),"/",TEXT(DATE(YEAR(O2),MONTH(O2),DAY(O2)),"mmmm")))

This seems too cumbersome but it is functional.