# Formula to display "October/November" [closed]

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?).

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-03-04 23:45:13.784346

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

( 2014-10-16 16:54:46 +0200 )edit

Sort by » oldest newest most voted

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)).

more

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)

more

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".

( 2014-10-16 18:35:06 +0200 )edit

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

( 2014-10-16 21:38:17 +0200 )edit

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.

more

## Stats

Asked: 2014-10-16 16:20:27 +0200

Seen: 269 times

Last updated: Oct 16 '14