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.

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.

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.

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.

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.

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.

=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.

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.

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.