Not sure how to shorten a formula

I created this formula:

=IF(B8="Jan","Feb",IF(B8="Feb","Mar",IF(B8="Mar","Apr",IF(B8="Apr","May",IF(B8="May","Jun",IF(B8="Jun","Jul",IF(B8="Jul","Aug",IF(B8="Aug","Sep",IF(B8="Sep","Oct",IF(B8="Oct","Nov",IF(B8="Nov","Dec",IF(B8="Dec","Jan"))))))))))))

so that when the user chooses a month abbreviation then next cell has the consecutive month IE: user enters Oct, next cell becomes Nov.

However i find using this type of formula is clunky, long, and in the long run will slow down my document.

If you have any ideas or know how to reduce this formula that would be much appreciated.

polarbearmike

Posted 2018-05-31T12:19:31.987

Reputation: 41

Answers

You can use EOMONTH, which allows the month offset with the second criterion:

=TEXT(EOMONTH(B8 & " 1",1),"mmm")

The B8 & " 1" creates a string that Excel can interpret as a date.

The EOMONTH(...,1) returns the last day of the next month.

The TEXT(...,"mmm") formats that date as just the abbreviated month name.

Scott Craner

Posted 2018-05-31T12:19:31.987

Reputation: 16 128

never used EOMONTH before, great answer ! – PeterH – 2018-06-01T08:21:20.607

Try one of: (the first is my improved answer in light of PeterH's answer, the second is my original answer)

=TEXT(DATE(2000, MONTH(1 & B8) + 1, 1), "mmm")
=TEXT(DATE(2000, MONTH(DATEVALUE(B8 & " 1")) + 1, 1), "mmm")

This automatically handles the rollover from 12 + 1 = 13 to 1 (interpreted as the first month of the following year).

The DATEVALUE() function will take the textual date, and return the date's "serial number". In our case we are using DATEVALUE(B8 & " 1")... but why?

Simply using DATEVALUE("Jan") will fail with a #VALUE! error.

To work around this, we tack on a " 1" to make it DATEVALUE("Jan 1"), which Excel happly interprets as "1 Jan 2001" - luckily we don't care about the year.

YEAR(DATEVALUE("Jan 1")) == 2001

As per PeterH's answer, MONTH(DATEVALUE(B8 & " 1")) can be simplified to just MONTH(1 & B8), also shown above.

Here 1 & B8 resolves to 1Jan, which Excel parses happily.

Attie

Posted 2018-05-31T12:19:31.987

Reputation: 14 841

This worked perfectly thanks! But, do you mind explaining to me the datevalue(B8 & " 1") part. – polarbearmike – 2018-05-31T13:04:48.287

Sure see my update. – Attie – 2018-05-31T15:19:16.280

Another option would be to use CHOOSE:

=CHOOSE(MATCH(B8,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),"FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC","JAN")

Or to simplify even further you could use MONTH to calculate which month number is in B8, then use this as the index value in CHOOSE:

=CHOOSE(MONTH(1&LEFT(B8,3)),"FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC","JAN")

PeterH

Posted 2018-05-31T12:19:31.987

Reputation: 5 346

-2

With different approach I would like to suggest you that how Month name can be cycled in adjacent cell.

Check the Screen Shot:

Follow these steps:

:Edited:

Create list of months using only three characters, as I've created in Column A.
Select list of months in Column A & name the Range, the given name is, MnthName.
In Cell D91 apply Data Validation, under "Setting Tab" pick "List" and in "Source" write the Named Range, MnthName.
In Cell F91 write this Formula:
=TEXT(DATE(2018,MATCH($D$91,MnthName,0)+COLUMNS($F$91:F91),1),"mmm").
Select the month name from the Drop down list, in Cell F91 you find the next moth's name.

N.B.

Adjust the Cell address as per your need.

Rajesh S

Posted 2018-05-31T12:19:31.987

Reputation: 6 800

The question states that the reference input value, "A", is any three-letter month abbreviation, and the desired output value, "B", is the three letter abbreviation of the following month. So any responsive answer has to start with "A" and produce "B". Maybe you can use a method related to what's described here to do that, but I'm not seeing anything that starts with "A" and produces "B". It also isn't clear what LstMonth refers to, or what the formula in F91 is supposed to do or why you coded it that way. (cont'd) – fixer1234 – 2018-06-03T06:14:45.713

And how would you scale this to a workbook full of source values? Can you revise the answer to directly address the requirements in the question and explain what the code does? Also, if you're going to handle this as a lookup, why can't you simply use INDEX & MATCH on one lookup table? – fixer1234 – 2018-06-03T06:14:51.907

@fixer1234, let me work out around it, & let me just refer other answers ,soon I'll return to the issue. – Rajesh S – 2018-06-03T06:26:17.290

@fixer1234, now I've edited the answer and replaced the Full Moth's name in column A with 3 characters Jan, feb (abbreviation). I feel the INDEX & MATCH is not an appropriate combination. Since in adjacent cell the next moth's name is expected so the DATE formula is best fitted in this situation. **Cont,,,, ** – Rajesh S – 2018-06-03T08:55:09.787

Since the formula I've used is not a hard nut to crack and to avoid the lengthy answer I don't feel to explain it there. But let me say precisely, the basic formula DATE(2018,,,,,) returns the first date of the moth like for SEP in D91 it returns 01/10/2018 in F91. With Date the year "2018" specifies the year. The other part +COLUMNS($F$91:F91) returns +1 which adds 1 to Month and finally warped with TEXT to get the month name in 3 charterers. This formula should written like this also,,, =TEXT(DATE(2018,MATCH($D$91,MnthName,0)+1,1),"mmm"). – Rajesh S – 2018-06-03T09:09:29.913

You've set up a bunch of convoluted stuff--named range, lookup table, data validation, MATCH, COLUMNS. But that's all unnecessary smoke and mirrors. You can strip it all away and get the same result using just =TEXT(DATE(2018,MONTH(B8&" 1")+1,1),"mmm") (using the OP's input cell reference), which is the equivalent of Attie's answer. That, in turn, is less efficient than Scott Craner's answer. So it isn't clear what "different approach" is really being used here, or what any of this really contributes as a solution method. – fixer1234 – 2018-06-04T03:04:04.917

A different way to phrase the point: you're converting the month to the month number as the mechanism for your formula. To do that, you set up infrastructure + a lookup to derive the month number. Sure, you can do it that way, but why would you jump through all of those hoops when you can do it directly with a function that does exactly that? There are endless "Rube Goldberg" contrivances that could convert the month to the month number, but that's using a complicated workaround for what's built-in. It doesn't make sense to use a lookup just because it's possible to do it that way. – fixer1234 – 2018-06-04T10:38:57.870