Subtract 1, 2 and 3 months from the text "September"
 

I have column A with the text string "September" and I need to have
column B show "September" minus 1 month or "August". Column C "July"
and D "June"

Then next month column A will change to October and B,C&D will move up
a month.

Make sense???
Thanks in advance for your help.

Subtract 1, 2 and 3 months from the text "September"
 

So what results do expect if A = January?

--
Biff
Microsoft Excel MVP


"wx4usa" wrote in message
...
I have column A with the text string "September" and I need to have
column B show "September" minus 1 month or "August". Column C "July"
and D "June"

Then next month column A will change to October and B,C&D will move up
a month.

Make sense???
Thanks in advance for your help.

Subtract 1, 2 and 3 months from the text "September"
 

then, may as wel make A1 as TODAY() and format cells, custom, mmmm

then B1 would be =DATE(year(A1),month(a1)-1,Day(A1))

And C1 would be =DATE(year(A1),month(a1)-2,Day(A1))

etc.

and this will also work for January counting back to December...

"wx4usa" wrote:

I have column A with the text string "September" and I need to have
column B show "September" minus 1 month or "August". Column C "July"
and D "June"

Then next month column A will change to October and B,C&D will move up
a month.

Make sense???
Thanks in advance for your help.

Subtract 1, 2 and 3 months from the text "September"
 

On Oct 13, 1:57*pm, Sean Timmons
wrote:
then, may as wel make A1 as TODAY() and format cells, custom, mmmm

then B1 would be =DATE(year(A1),month(a1)-1,Day(A1))

And C1 would be =DATE(year(A1),month(a1)-2,Day(A1))

etc.

and this will also work for January counting back to December...

"wx4usa" wrote:
I have column A with the text string "September" and I need to have
column B show "September" minus 1 month or "August". Column C "July"
and D "June"

Then next month column A will change to October and B,C&D will move up
a month.

Make sense???
Thanks in advance for your help.

I have column A with the Month September. Then column B needs to say
August, Then June so I can use these columns in a sumproduct formula
for the last 3 months sales. I dont want to have to go in and type
the other 2 months in because someone else will be doing entry.

Subtract 1, 2 and 3 months from the text "September"
 

On Oct 13, 1:53*pm, "T. Valko" wrote:
So what results do expect if A = January?

--
Biff
Microsoft Excel MVP

"wx4usa" wrote in message

...

I have column A with the text string "September" and I need to have
column B show "September" minus 1 month or "August". Column C "July"
and D "June"

Then next month column A will change to October and B,C&D will move up
a month.

Make sense???
Thanks in advance for your help.

Oh If January, December. Is that possible? The spreadsheet is
already formatted with Month, Day and Year in separate columns.

Subtract 1, 2 and 3 months from the text "September"
 

Put this in B1 and fill to D1:
=TEXT(DATE(YEAR(DATEVALUE(A1&" 1, 2000")),MONTH(DATEVALUE(A1&" 1,
2000"))-1,1),"mmmm")


"wx4usa" wrote in message
...
I have column A with the text string "September" and I need to have
column B show "September" minus 1 month or "August". Column C "July"
and D "June"

Then next month column A will change to October and B,C&D will move up
a month.

Make sense???
Thanks in advance for your help.

Subtract 1, 2 and 3 months from the text "September"
 

=TEXT(DATE(YEAR(NOW()),MONTH(DATEVALUE(A1&" 1, " & YEAR(NOW())))-1,1),"MMMM")
=TEXT(DATE(YEAR(NOW()),MONTH(DATEVALUE(A1&" 1, " & YEAR(NOW())))-2,1),"MMMM")
=TEXT(DATE(YEAR(NOW()),MONTH(DATEVALUE(A1&" 1, " & YEAR(NOW())))-3,1),"MMMM")

HTH,
Bernie
MS Excel MVP


"wx4usa" wrote in message
...
I have column A with the text string "September" and I need to have
column B show "September" minus 1 month or "August". Column C "July"
and D "June"

Then next month column A will change to October and B,C&D will move up
a month.

Make sense???
Thanks in advance for your help.

Subtract 1, 2 and 3 months from the text "September"
 

The formulas above wouldn't require the columns to be entered. The formulas
would be there. The user would need only enter, say, 9/1 into A, and your
other columns would become 8/1 and 7/1. If formatted as Custom/mmmm, thery
will show as august and July.

"wx4usa" wrote:

On Oct 13, 1:57 pm, Sean Timmons
wrote:
then, may as wel make A1 as TODAY() and format cells, custom, mmmm

then B1 would be =DATE(year(A1),month(a1)-1,Day(A1))

And C1 would be =DATE(year(A1),month(a1)-2,Day(A1))

etc.

and this will also work for January counting back to December...

"wx4usa" wrote:
I have column A with the text string "September" and I need to have
column B show "September" minus 1 month or "August". Column C "July"
and D "June"

Then next month column A will change to October and B,C&D will move up
a month.

Make sense???
Thanks in advance for your help.

I have column A with the Month September. Then column B needs to say
August, Then June so I can use these columns in a sumproduct formula
for the last 3 months sales. I dont want to have to go in and type
the other 2 months in because someone else will be doing entry.

Subtract 1, 2 and 3 months from the text "September"
 

Try this...

A1 = some month name as TEXT in the form September

Create this named formula...
Goto the menu InsertNameDefine
Name: Months
Refers to:

="DecNovOctSepAugJulJunMayAprMarFebJanDecNov"

Ok out

Enter this formula in B1 and copy across to D1:

=TEXT("1-"&MID(Months,SEARCH(LEFT(A1,3),Months)+3,3),"mmmm" )

--
Biff
Microsoft Excel MVP


"wx4usa" wrote in message
...
On Oct 13, 1:53 pm, "T. Valko" wrote:
So what results do expect if A = January?

--
Biff
Microsoft Excel MVP

"wx4usa" wrote in message

...

I have column A with the text string "September" and I need to have
column B show "September" minus 1 month or "August". Column C "July"
and D "June"

Then next month column A will change to October and B,C&D will move up
a month.

Make sense???
Thanks in advance for your help.

Oh If January, December. Is that possible? The spreadsheet is
already formatted with Month, Day and Year in separate columns.