how to calculate a month without the full date?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
=TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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.
 
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)
 
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)
 
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
 
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)
 
Hi Bob,

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

Andy.
 
Thanks it worked!!!!




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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
Thanks you all for everything.
Now how do i do this backwards?

I input May
The resolt : April?

Jared
 
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
 
Yes. Perfet!

Thanks







Ron Coderre said:
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
Click to expand...
 
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)
 
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
 
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
 
Back
Top