Calculate the next month 1

[jh3016]

I have an Excel worksheet. In cell A2, I have the month "April". In cell B2, I would like to have this calculate one month from A2. So in other words, if I type in April in cell A2, I want May to automatically populate in cell B2.

Thanks in advance.

 

[ETID]

Here's a function that you will need to paste into a module

(alt+f11) Insert>Module

then the formula is =Next_Month(Proper(a1))

'*******************************************
'cut and paste here to end

Function Next_Month(incell As String) As String

On Error GoTo err_hit

l = Len(incell)


month_array = Array("January", "February", "March", _
"April", "May", "June", "July", _
"August", "September", "October", _
"November", "December")

For i = 0 To 11 Step 1

If Left(incell, 3) = Left(month_array(11), 3) Then
If l = 3 Then
Next_Month = Left(month_array(0), 3)
Else
Next_Month = month_array(0)
End If

Else
If Left(month_array(i), 3) = Left(incell, 3) Then
If l = 3 Then
Next_Month = Left(month_array(i + 1), 3)
Else
Next_Month = month_array(i + 1)
End If
Else
End If
End If
Next i

Exit Function
err_hit:
Exit Function

End Function

 

[TonyJollans]

Hi jh3016,

Assuming you just have a month name as a string, converting it to a date, adding a month and converting it back should do the trick. In B2 ..

[blue][tt]=TEXT(DATE(2000,MONTH("01/"&A2&"/2000")+1,1),"mmmm")[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[SkipVought]

jh3016,

FYI & FFR (for future reference)

You may have been "Stuck" with data that you inherited from another person.

But when you CREATE your own data/stucture, that has ANYTHING to do with dates (ie days, months, years) use REAL DATES!

Real Excel Dates are simply NMBERS. Like Today is 38080, however, when you enter a date, you usually enter something like 4/3/2004. Excel is SMART enough to RECOGNIZE that you have entered a DATE, CONVERTS the sring of characters that you entered to a NUMBER and FOMATS the NUMBER to display as 4/3/2004.

THEN...
if you were to CHANGE the format in Format/Cells - Number Tab, Category: Custom, Type: mmmm and [OK]...

your DATE would be DISPLAYED as...

April

The date VALUE is STILL 38080!

Entering date-related data as a REAL DATE makes REAL SENSE and aids in the manipulation of the data.




Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Storyteller]

Hello All,
In keeping with "there is always another way", if you are planning to write your own code to solve this question. I would suggest using this function:

DateAdd()

Syntax: DateAdd(interval, number, date)

so to add one month

DateAdd("m", 1, A2)

Hope this helps,
Michael