Have not thought of EDATE. Thank you very much. I can solve from here on. Thanks again
Sent via BlackBerry Wireless -----Original Message----- From: Paul Schreiner <schreiner_p...@att.net> Date: Thu, 18 Jun 2009 07:01:58 To: <excel-macros@googlegroups.com> Subject: $$Excel-Macros$$ Re: Subtracting 1 to find the previous month. What to do at Jan? 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
