Is there a more efficient way to show a subsequent month, but in number format?

[smhelgerson49]

I'm looking for a more efficient way to show the subsequent month in a series. The month format I have to use is YYYYMM, but it has to be in number format. It has to do with the people who are entering the information. Explaining to them to enter a month as 10/2021, for example, is actually confusing to them, so I have them enter it as 202110. I use these numbers to calculate the difference between meter readings during a given period of time.
The best solution I've come up with is a strange combination of SUMIFS, VLOOKUP, and a table of sequential months. I'm pretty sure there's a more efficient way, but just can't figure it out.

Any thoughts?

Thanks.

[AliGW]

Maybe this?

=CHOOSE(--RIGHT(A1,2),2,3,4,5,6,7,8,9,10,11,12,1)

Bear in mind you haven't actually told us exactly what outcome you want - this should return the NUMBER of the following month.

[Glenn Kennedy]

A different interpretation of what you want... and a different formula!!!


=LEFT(A3,4)+INT((RIGHT(A3,2)+1)/12)&TEXT(MOD(RIGHT(A3,2)+1,12),"00")

[smhelgerson49]

That works pretty slick, actually. Took me a moment to figure out what you did there.
Is there a way that I can have this date format show up if I select a month from a drop down? In other words, if I select "JUN" from a drop down, and "2022" from another drop down, it would return 202206, then I could use your formula for the subsequent month of 202207.

[Glenn Kennedy]

Who are you talking to?

[smhelgerson49]

Who are you talking to?

That was for you, Mr. Kennedy...
Sorry...still learning this forum etiquette.

[Glenn Kennedy]

If it was me...

formula to generate "entered month" becomes:

=IF(OR(A3="",B3=""),"",A3&TEXT(MONTH(1&B3),"00"))

see file for context.

In any event, please make it clear WHO you are replying to...

[smhelgerson49]

Maybe this?

=CHOOSE(--RIGHT(A1,2),2,3,4,5,6,7,8,9,10,11,12,1)

Bear in mind you haven't actually told us exactly what outcome you want - this should return the NUMBER of the following month.

Good point! Thank you for always helping me improve my posts!

More specifically, I need to have a drop down list of short months (JAN, FEB, MAR...) and an associated year from a drop down (2019, 2020, etc...) then return that month in a YYYYMM format (so JUN 2023 selected from the drop down lists would give me 202306, but in a number format rather than date. Then, I'd need the subsequent month (202307) also in number format. The reason I need them in number format is that the folks I have doing meter readings are military and think in the YYYYMM format when they enter dates. All the data I have going back to 2010 is in that format.

Does that clarify?

[Glenn Kennedy]

This is more logical:

=IF(A3="","",TEXT(DATE(A3,MONTH(1&B3)+1,1),"yyyymm")+0)

[smhelgerson49]

If it was me...

formula to generate "entered month" becomes:

=IF(OR(A3="",B3=""),"",A3&TEXT(MONTH(1&B3),"00"))

see file for context.

In any event, please make it clear WHO you are replying to...

If Marvel ever makes a movie about Excel Superheros...you'll be the Iron Man of that story arch. This is amazing.