[SOLVED] how to calculate a month without the full date?

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)

"Jared" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
>
> --
> HTH
>
> Bob Phillips
>
Hi,

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

Regards,
Bondi

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" <[email protected]> wrote in message
news:[email protected]...
> =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Jared" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

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)

"Bondi" <[email protected]> wrote in message
news:[email protected]...
>
> Bob Phillips wrote:
> > =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> Hi,
>
> In case you run in to February you should change 30 to 28 and 2 to 5.
>
> Regards,
> Bondi
>

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)

<Andy> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Jared" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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

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" wrote:

> =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Jared" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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)

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Jared" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

Hi Bob,

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

Andy.

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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)
>
> <Andy> wrote in message news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (replace somewhere in email address with gmail if mailing direct)
>> >
>> > "Jared" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >
>> >
>>
>>
>
>

Thanks it worked!!!!




"Bob Phillips" wrote:

> =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Jared" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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

I input May
The resolt : April?

Jared


"Bob Phillips" wrote:

> 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)
>
> "Ron Coderre" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "Jared" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

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


"Jared" wrote:

> Thanks you all for everything.
> Now how do i do this backwards?
>
> I input May
> The resolt : April?
>
> Jared
>
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "Ron Coderre" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > > > =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "Jared" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

Yes. Perfet!

Thanks







"Ron Coderre" wrote:

> 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
>
>
> "Jared" wrote:
>
> > Thanks you all for everything.
> > Now how do i do this backwards?
> >
> > I input May
> > The resolt : April?
> >
> > Jared
> >
> >
> > "Bob Phillips" wrote:
> >
> > > 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)
> > >
> > > "Ron Coderre" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" wrote:
> > > >
> > > > > =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace somewhere in email address with gmail if mailing direct)
> > > > >
> > > > > "Jared" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

Hi Bob and Andy

No particular reason for using 5 here either..

Regards,
Bondi

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)

"Bondi" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Bob and Andy
>
> No particular reason for using 5 here either..
>
> Regards,
> Bondi
>