What function/formula can I use to return the previous month. Let's say cell
A1 contains just the word August. What can I put in the next cell to
return July. But will also give December if January is in A1. I am
currently doing it with the following convoluted vlookup & I'm sure there
must be an easier way.

ColG ColH ColI

12 December 12
11 November 11
10 October 10
9 September 9
8 August 8
7 July 7
6 June 6
5 May 5
4 April 4
3 March 3
2 February 2
1 January 1
0 December 0
-1 November -1
-2 October -2

Above is my vlookup range (columns G,H,I rows 3 to 17) and my formula is:

=VLOOKUP((VLOOKUP(A1,$H$3:$I$17,2,FALSE)-1),$G$3:$H$17,2,FALSE)

It works fine but I'm sure I must be missing something *really* obvious.(and
simpler:-)

Many thanks

-Jay-

Hi Jay,

Use this formula

=TEXT(("1 "&A1)-1,"mmmm")

where A1 contains the Month in text, i.e. "August" or "Aug"


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=555357

Try...

=TEXT(DATEVALUE(A1&" 2006")-1,"mmmm")

Hope this helps!

In article ,
Jay wrote:

What function/formula can I use to return the previous month. Let's say cell
A1 contains just the word August. What can I put in the next cell to
return July. But will also give December if January is in A1. I am
currently doing it with the following convoluted vlookup & I'm sure there
must be an easier way.

ColG ColH ColI

12 December 12
11 November 11
10 October 10
9 September 9
8 August 8
7 July 7
6 June 6
5 May 5
4 April 4
3 March 3
2 February 2
1 January 1
0 December 0
-1 November -1
-2 October -2

Above is my vlookup range (columns G,H,I rows 3 to 17) and my formula is:

=VLOOKUP((VLOOKUP(A1,$H$3:$I$17,2,FALSE)-1),$G$3:$H$17,2,FALSE)

It works fine but I'm sure I must be missing something *really* obvious.(and
simpler:-)

Many thanks

-Jay-

=TEXT(DATE(1901,MONTH(DATEVALUE("1 " &A1))-1,1),"mmmm")

HTH
--
AP

"Jay" a écrit dans le message de news:
...
What function/formula can I use to return the previous month. Let's say
cell
A1 contains just the word August. What can I put in the next cell to
return July. But will also give December if January is in A1. I am
currently doing it with the following convoluted vlookup & I'm sure there
must be an easier way.

ColG ColH ColI

12 December 12
11 November 11
10 October 10
9 September 9
8 August 8
7 July 7
6 June 6
5 May 5
4 April 4
3 March 3
2 February 2
1 January 1
0 December 0
-1 November -1
-2 October -2

Above is my vlookup range (columns G,H,I rows 3 to 17) and my formula is:

=VLOOKUP((VLOOKUP(A1,$H$3:$I$17,2,FALSE)-1),$G$3:$H$17,2,FALSE)

It works fine but I'm sure I must be missing something *really*
obvious.(and
simpler:-)

Many thanks

-Jay-

Data in columns G and H:

=VLOOKUP(A1,G3:H14,2,FALSE)


G H
January December
February January
March February
April March
May April
June May
July June
August July
September August
October September
November October
December November

OR



=LOOKUP(A1,{"April";"August";"December";"February" ;"January";"July";"June";"March";"May";"November"; "October";"September"},{"March";"July";"November"; "January";"December";"June";"May";"February";"Apri l";"October";"September";"August"})

HTH

"Jay" wrote:

What function/formula can I use to return the previous month. Let's say cell
A1 contains just the word August. What can I put in the next cell to
return July. But will also give December if January is in A1. I am
currently doing it with the following convoluted vlookup & I'm sure there
must be an easier way.

ColG ColH ColI

12 December 12
11 November 11
10 October 10
9 September 9
8 August 8
7 July 7
6 June 6
5 May 5
4 April 4
3 March 3
2 February 2
1 January 1
0 December 0
-1 November -1
-2 October -2

Above is my vlookup range (columns G,H,I rows 3 to 17) and my formula is:

=VLOOKUP((VLOOKUP(A1,$H$3:$I$17,2,FALSE)-1),$G$3:$H$17,2,FALSE)

It works fine but I'm sure I must be missing something *really* obvious.(and
simpler:-)

Many thanks

-Jay-

On 25/6/06 15:04, in article
, "daddylonglegs"
wrote:


Hi Jay,

Use this formula

=TEXT(("1 "&A1)-1,"mmmm")

where A1 contains the Month in text, i.e. "August" or "Aug"


Daddylonglegs,

Well that's certainly a *lot* less work than mine:-) Can you just parse it
for me (if you don't mind) I'm familiar with the text Ÿn and the -1 must be
giving the previous month, but what does the "1 "& do?

Many thanks

-Jay-

If A1 contains "August" then "1 "&A1 will be "1 August" which excel will
evaluate as a date - 1st August in the current year, i.e. 1st August
2006.

Subtracting 1 will then give you the previous day, 31st July 2006 and
the text function will extract just the month.

You could also reverse the "1 "&A1 to A1&" 1" - you'll get the same
result


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=555357

On 25/6/06 18:23, in article
, "daddylonglegs"
wrote:


If A1 contains "August" then "1 "&A1 will be "1 August" which excel will
evaluate as a date - 1st August in the current year, i.e. 1st August
2006.

Subtracting 1 will then give you the previous day, 31st July 2006 and
the text function will extract just the month.

You could also reverse the "1 "&A1 to A1&" 1" - you'll get the same
result


I get it now:

€ concatenate a 1 to the month to give 1st month
€ -1 gives last day of previous month
€ Text Ÿn (format mmmm)converts it back to text, with just month,no date

Nice. Thanks a lot.

-Jay-