how to calculate a month without the full date?

[Guest]

This might be a stupid question.

i have a cell (A1), with a month's name: "April"
How do i use a formula so cell B1 will show "May"

and when i change A1 to "June" Cell B1 will show "July"?

Basically a calulation but without a full date

Thanks
Jared

 

[Bob Phillips]

=TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bondi]

=TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")

Hi,

In case you run in to February you should change 30 to 28 and 2 to 5.

Regards,
Bondi

 

[Guest]

Hi

Nice one Bob!!
On my machine, though, it falls over on February. This should help:
=TEXT(DATEVALUE("28-"&A1&"-"&YEAR(TODAY()))+5,"mmmm")

Andy.

 

[Bob Phillips]

Good point. I originally tried 32 without the add, but DateValue didn't like
it. Forgot Feb on my final try.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Hi Andy,

You did the same as Bondi, added 5. Why 5? I would have added 4 (if I had
remembered Feb <g>).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob

I think we can just let Excel default the year in (since YEAR doesn't seem
to matter in this case):

B1: =TEXT(DATEVALUE("28-"&A1)+4,"mmmm")

***********
Regards,
Ron

XL2002, WinXP

 

[Bob Phillips]

We can Ron, but I am not a great believer in defaulting, it invariably comes
back and bites you when you least expect it.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Hi Bob,

Why 5? Erm, why not? Seems strange, though, that Bondi chose 5 too!! Just
making sure to give it enough! <vbg>

Andy.

 

[Guest]

Thanks it worked!!!!

=TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks you all for everything.
Now how do i do this backwards?

I input May
The resolt : April?

Jared

 

[Guest]

Here you go....Try this:

A1: (a month name)

The name of the previous month
B1: =TEXT(DATEVALUE("1-"&A1&"-"&YEAR(TODAY()))-1,"mmmm")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Yes. Perfet!

Thanks

Here you go....Try this:

A1: (a month name)

The name of the previous month
B1: =TEXT(DATEVALUE("1-"&A1&"-"&YEAR(TODAY()))-1,"mmmm")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Bondi]

Hi Bob and Andy

No particular reason for using 5 here either..

Regards,
Bondi

 

[Bob Phillips]

Just odd how you both came up with it. I think I will add 17 <vbg>

Seriously, it is a pity you can't enter 32 directly, you have to give a
valid day and add onto the date.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Epinn]

I suspected that the following formula might not work for February. So, I
did a test and I got an error.

For it to work for all 12 months, I think we have to change 30 to 28 and
also change +2 to +4.

It is interesting that it takes care of December.

Epinn

 

[Epinn]

When I posted previously, I only saw the formula (below). I only found out
about the rest of the thread now.

For the record, I did pick "4" instead of "5."

Very interesting formula.

Epinn