Function to give previous month

[Jay]

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-

 

[daddylonglegs]

Hi Jay,

Use this formula

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

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

 

[Domenic]

Try...

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

Hope this helps!

 

[Ardus Petus]

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

HTH

 

[Guest]

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";"April";"October";"September";"August"})

HTH

 

[Jay]

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-

 

[daddylonglegs]

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

 

[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

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-