 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2006Month end calculation
I am writing a formula to calculate the last and next month end e.g. if I enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06. 28-02-06 will be stored in cell A1, and my expected result will be displayed in A2 & A3. My formular is liked " =A1-31 , = A1+31. But because of "31" has to change each month, therefore if doesn't work to my calcaulation. Also, from the above example, the calculation for March is correct "31-03-06" but the January is worng, it comes date on 28-01-06. But I need both result at the end of the month. Can anyone help, thank you so much. If you're always entering the last day of a month in A1 then for th last day of the previous month =A1-DAY(A1) and for the last day of the next month =A1+32-DAY(A1+32) alternatively yu could use the EOMONTH function from Analysis ToolPa and use =EOMONTH(A1,-1) and =EOMONTH(A1,1 =EOMONTH(A1,-1) =EOMONTH(A1,1) > I am writing a formula to calculate the last and next month end e.g. if I > enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06. [quoted text clipped - 4 lines] > but the January is worng, it comes date on 28-01-06. But I need both result > at the end of the month. Can anyone help, thank you so much. the formula doesn't work, it came #NAME? as result. I don't know why. Thanks Tony > =EOMONTH(A1,-1) > =EOMONTH(A1,1) [quoted text clipped - 7 lines] > > but the January is worng, it comes date on 28-01-06. But I need both result > > at the end of the month. Can anyone help, thank you so much. the analysis toolpak has to be installed. Tools/Add-Ins/Analysis Tookpak > the formula doesn't work, it came #NAME? as result. I don't know why. > [quoted text clipped - 12 lines] > > > but the January is worng, it comes date on 28-01-06. But I need both result > > > at the end of the month. Can anyone help, thank you so much. Thanks JMB I had installed the Tookpak and re-start my computer as well. I tried to re-type my formular again and again. Also I did try to copy your formular and paste, also doesn't work. I don't understand it. Thank you for your help. Tony > the analysis toolpak has to be installed. > [quoted text clipped - 16 lines] > > > > but the January is worng, it comes date on 28-01-06. But I need both result > > > > at the end of the month. Can anyone help, thank you so much. >I am writing a formula to calculate the last and next month end e.g. if I >enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06. [quoted text clipped - 4 lines] >but the January is worng, it comes date on 28-01-06. But I need both result >at the end of the month. Can anyone help, thank you so much. A1: Some Date A2: =A1-DAY(A1) (End of month prior to Some Date) A3: =A2+63-DAY(A2+63) (End of month in month after Some Date) --ron Ron Rosenfeld wrote... ... >A1: Some Date >A2: =A1-DAY(A1) (End of month prior to Some Date) >A3: =A2+63-DAY(A2+63) (End of month in month after Some Date) February is the curse of all such simplifications. Put =DATE(2006,2,28) in A2. A3 then returns 30-Apr-2006. Put =DATE(2006,9,29) and =DATE(2006,8,30) in A2, and see that it's not just February. The most reliable way to do this with built-in functions is =DATE(YEAR(A2),MONTH(A2)+2,0) and generally the equivalent for EOMONTH(x,n) is =DATE(YEAR(x),MONTH(x)+1+n,0) >Ron Rosenfeld wrote... >... [quoted text clipped - 13 lines] > >=DATE(YEAR(x),MONTH(x)+1+n,0) Harlan, Either you're sleepy or I'm missing the point you are trying to make. Clearly if you change one of my formulas, you cannot expect the algorithm to work! However, with regard to your formulas, if you force A2 to be 28 Feb 2006, then, as designed, A1 must have been a date in March, and A3 should, indeed be 30-Apr-2006 per the OP's request. However, if you expect A3 to compute correctly when you are not forcing A2 to be an end of month date, you are not understanding what I posted. Entering your example dates in the DATA ENTRY CELL, A1, with the formulas I posted being in A2 and A3, gives the following results: A1: =DATE(2006,2,28) A2: 31-Jan-2006 A3: 31-Mar-2006 And A1: =DATE(2006,9,29) A2: 31-Aug-2006 A3: 31-Oct-2006 --ron Ron Rosenfeld wrote... >>Ron Rosenfeld wrote... ... >>>A1: Some Date >>>A2: =A1-DAY(A1) (End of month prior to Some Date) >>>A3: =A2+63-DAY(A2+63) (End of month in month after Some Date) ... OK, it's a 2-fold thing. I missed that. Sorry. So A3 would be the end of the month in the month after A1 rather than A2. Your formulas work for that. However, two cells to obtain one result is wasteful. It's still easier to use =DATE(YEAR(x),MONTH(x)+1+n,0) in one cell to get the same result as EOMONTH(x,n). The exception is EOMONTH(x,-1), in which case your A2 formula =x-DAY(x) is best. Thank you so much Ron & Harlan I got a little bit problem with this "=A2+63-DAY(A2+63) (End of month in month after Some Date)", because I don't know how many month(s) before or after I need to calcaulate until my boss will ask me. if if has me 3 months after then I have to get the result at once, but from your formula I have to change the no. of days to calculate. I will try to use the eomonth formula, but meanwhile it doesn't work with my computer. I don't understand. Thank you so much for great help. Tony > >Ron Rosenfeld wrote... > >... [quoted text clipped - 42 lines] > > --ron >Thank you so much Ron & Harlan > [quoted text clipped - 7 lines] >Thank you so much for great help. >Tony If the number of months after "some date" is a variable, then use this formula instead: A1: SomeDate B1: Number of Months after the month of SomeDate for A3 A2: =A1-DAY(A1) (end of month prior to some date) A3: =DATE(YEAR(A2),MONTH(A1)+1+B1,0) (end of month that is B1 months after the month of SomeDate) --ron Just FYI at this point, but for EOMONTH, the analysis toolpak has to be installed, then enabled through the Tools/Add-Ins menu. Harlan's formula should have worked fine for you and can adapt easily to different numbers of months > > >=DATE(YEAR(x),MONTH(x)+1+n,0) Where x is the cell containing the first date, and n is the number of months before or after the first date. Set up another cell for the number of months and just refer to that cell in the formula. > Thank you so much Ron & Harlan > [quoted text clipped - 54 lines] > > > > --ron Thank you so much, it works now. Tony > Just FYI at this point, but for EOMONTH, the analysis toolpak has to be > installed, then enabled through the Tools/Add-Ins menu. [quoted text clipped - 68 lines] > > > > > > --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.