Function to give previous month
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- |
Function to give previous month
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 |
Function to give previous month
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- |
Function to give previous month
=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- |
Function to give previous month
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- |
Function to give previous month
|
Function to give previous month
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 |
Function to give previous month
|
All times are GMT +1. The time now is 06:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com