Ask Your Question
0

Formula to display "October/November" [closed]

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

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

Comments

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 +0100 )edit

3 Answers

Sort by » oldest newest most voted
1

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

Lupp gravatar image

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

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
0

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

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

edit flag offensive delete link more

Comments

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 +0100 )edit

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

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

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

Chris Vella gravatar image

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

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

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

Seen: 262 times

Last updated: Oct 16 '14