| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
=TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm")
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jared" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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" wrote in message ... =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jared" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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" wrote in message oups.com... 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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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 ... 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" wrote in message ... =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jared" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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" wrote in message ... 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" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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" wrote in message ... 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 ... 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" wrote in message ... =TEXT(DATEVALUE("30-"&A1&"-"&YEAR(TODAY()))+2,"mmmm") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jared" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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" wrote in message ... 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" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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" wrote in message ... 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" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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" wrote in message ... 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" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
Hi Bob and Andy No particular reason for using 5 here either.. Regards, Bondi |
|
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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" wrote in message ups.com... Hi Bob and Andy No particular reason for using 5 here either.. Regards, Bondi |
|
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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 "Jared" wrote in message ... 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" wrote in message ... 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 |
|
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to calculate a month without the full date?
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 "Epinn" wrote in message ... 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 "Jared" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calculating month of present date | Excel Discussion (Misc queries) | |||
| extract the month of a date | Excel Discussion (Misc queries) | |||
| How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
| calculate anniversary of date after specified date | Excel Worksheet Functions | |||
| calculate no. of years between a date and today's date | Excel Worksheet Functions | |||
Linear Mode
