It depends on how you want to approach it. If You START with the original date, Edate can be used to get the date 1,2,3 or 4 months previous to it.. So, =month(edate(A1,-1)) would get you the month PREVIOUS to the date.. 2 months is =month(edate(A1,-2)) 3 months is =month(edate(A1,-3)) 4 months is =month(edate(A1,-4)) the year is =year(edate(A1,-1)) (or -2, -3, -4)
Now, if you're stuck with (or starting with) the month number, you can either convert it to a date and use the above: =MONTH(EDATE(DATEVALUE(A1&"/1/2009"),-1)) or to get the month, use something like: =IF($A1-1<=0,12+$A1-1,$A1-1) =IF($A1-2<=0,12+$A1-2,$A1-2) =IF($A1-3<=0,12+$A1-3,$A1-3) =IF($A1-4<=0,12+$A1-4,$A1-4) You didn't mention where you're "getting" the year, so I'm not sure what to recommend for getting the previous year. You could use: =IF($A1-1<=0,"2008","2009") but that doesn't prepare you for 6 months from now! I'd lean toward doing the conversion with the date you're getting from the database hope this helps, Paul ________________________________ From: Ahmet Yalcin <ahmetyalc...@gmail.com> To: excel-macros@googlegroups.com Sent: Thursday, June 18, 2009 7:26:06 AM Subject: $$Excel-Macros$$ Subtracting 1 to find the previous month. What to do at Jan? Hello You Helpful People :) My question today is: For example I use vlookup to get a month "4" (April) from a database. At this report sheet I also want to show the previous 4 months. What I do here is subtract 1 from April, to find March and subtract 2 to find February, etc.. How can I form my formula that when the month reaches 1 (January) and when I subtract 1 it should give me 12 instead of 0 and 2008 instead of 2009. Or what is the way of doing this? Thank you in advance once again Regards Ahmet --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
