Ask Your Question

Formula to display "October/November" [closed]

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

Chris Vella gravatar image

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 flag offensive 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.

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

3 Answers

Sort by » oldest newest most voted

answered 2014-10-16 21:44:46 +0200

Lupp gravatar image

updated 2014-10-16 21:45:28 +0200

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)).
edit flag offensive delete link more

answered 2014-10-16 17:29:33 +0200

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


    ) & "/" & TEXT(


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

edit flag offensive delete link 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".

Chris Vella gravatar imageChris Vella ( 2014-10-16 18:35:06 +0200 )edit

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

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

answered 2014-10-16 18:33:06 +0200

Chris Vella gravatar image

updated 2014-10-16 19:18:48 +0200

I found a solution myself with the following formula:


This seems too cumbersome but it is functional.

edit flag offensive delete link more

Question Tools

1 follower


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

Seen: 269 times

Last updated: Oct 16 '14