EOMONTH()
by Helmut Reinke Egbert Jeschke Sara Unverhau, Eckehard Pfeifer, Bodo Fienitz, and
Microsoft® Excel® 2010 Formulas & Functions Inside Out
- Microsoft® Excel® 2010 Formulas & Functions Inside Out
- A Note Regarding Supplemental Files
- Introduction
- 1. Solving Problems with Functions
- Introducing the New Functions in Excel 2010
- Scenario and Goals
- The New Way to Work with Data, Formulas, and Functions
- Entering Test Data Fast
- Converting Formula Results into Fixed Values
- Formatting Numeric Values
- Calculating Profit Margin
- Formatting Data as a Table
- Calculating Profit Margin as a Percentage
- Applying Conditional Formatting
- Creating Meaningful Charts
- Working with PivotTables
- Using PivotCharts: Graphical Pivot
- 2. Using Functions and PowerPivot
- Using Date and Time Functions
- The Excel Date System
- Date and Time Number Formats
- Leap Years
- Analysis Functions
- Date and Time Functions in Practice
- Calculating the Start and End of Daylight Saving Time
- Identifying What Day of the Year Today Is
- Calculating with Time
- Calculating Working Hours
- Calculating Beyond the Date Limit
- Adding Time Intervals
- Calculating the Salary for Working Hours
- Rounding Time Values
- Identifying the Quarter
- Converting Normal Hours into Decimal Hours (Industrial Hours)
- Using Text and Data Functions
- Separating Text Strings Such as ZIP Code and Location
- Separating First and Last Names
- Switching the First and Last Names
- Resolving the IBAN
- Calculating the Frequency of a Character in a String
- Removing All Spaces
- Correcting the Position of Signs
- Displaying the File and Worksheet Name
- Concatenating the Content of Cells
- Breaking Lines in Concatenated Text
- Visualizing Data
- Using Logical Functions
- Using Search and Reference Functions
- Using Information Functions
- Using Statistical Functions
- Using Database Functions
- Using Cube Functions
- Using Financial Math Functions
- Using Math and Trigonometry Functions
- PowerPivot
- 3. From Numbers to Formulas
- Entering Formulas
- Operators
- References in Formulas
- Array Formulas
- Tips and Tricks
- Turning the Formula View On and Off
- Entering Formulas in Several Cells
- Entering Formulas in Several Worksheets
- Selecting Formula Cells
- Determining Which Cells Are Referenced in a Formula
- Copying and Moving Formulas
- Moving Formula Cells
- Copying Formula Cells
- Converting Formula Results into Fixed Values
- Converting Existing Values
- Protecting Formulas
- Hiding Formulas
- Specifying the Formula Calculation Type
- Analyzing Formulas
- Troubleshooting: Example 1
- Troubleshooting: Example 2
- 4. Formulas and Functions
- 5. Functions in Special Operations
- Functions in Names
- Functions for Conditional Formatting
- Highlighting Weekends in Color
- Using the MATCH() Function
- Highlighting Identical Values
- Finding the Differences Between Tables on Different Worksheets
- List Print Layout
- Emphasizing the Top Three Elements
- Highlighting Cells Containing Spaces
- Navigating in Tables with Reference Lines
- Formatting Data Groups
- Formatting Subtotal Results
- Tips for Conditional Formatting in Excel 2003
- Tips and Troubleshooting for Conditional Formatting
- Functions for Validation
- 6. Custom Functions
- 7. Date and Time Functions
- 8. Text and Data Functions
- 9. Logical Functions
- 10. Lookup and Reference Functions
- 11. Information Functions
- 12. Statistical Functions
- AVEDEV()
- AVERAGE()
- AVERAGEA()
- AVERAGEIF()
- AVERAGEIFS()
- BETA.DIST()/BETADIST()
- BETA.INV()/BETAINV()
- BINOM.DIST()/BINOMDIST()
- BINOM.INV()/CRITBINOM()
- CHISQ.DIST()
- CHISQ.DIST.RT()/CHIDIST()
- CHISQ.INV()
- CHISQ.INV.RT()/CHIINV()
- CHISQ.TEST()/CHITEST()
- CONFIDENCE.NORM()/CONFIDENCE()
- CONFIDENCE.T()
- CORREL()
- COUNT()
- COUNTA()
- COUNTBLANK()
- COUNTIF()
- COUNTIFS()
- COVAR()
- COVARIANCE.P()
- COVARIANCE.S()
- DEVSQ()
- EXPON.DIST()/EXPONDIST()
- F.DIST()
- F.DIST.RT()/FDIST()
- F.INV()
- F.INV.RT()/FINV()
- F.TEST()/FTEST()
- FISHER()
- FISHERINV()
- FORECAST()
- FREQUENCY()
- GAMMA.DIST()/GAMMADIST()
- GAMMA.INV()/GAMMAINV()
- GAMMALN()
- GAMMALN.PRECISE()
- GEOMEAN()
- GROWTH()
- HARMEAN()
- HYPGEOM.DIST()/HYPGEOMDIST()
- INTERCEPT()
- KURT()
- LARGE()
- LINEST()
- LOGEST()
- LOGNORM.DIST()/LOGNORMDIST()
- LOGNORM.INV()/LOGINV()
- MAX()
- MAXA()
- MEDIAN()
- MIN()
- MINA()
- MODE.SNGL()/MODE()
- MODE.MULT()
- NEGBINOM.DIST()/NEGBINOMDIST()
- NORM.DIST()/NORMDIST()
- NORM.INV()/NORMINV()
- NORM.S.DIST()/NORMSDIST()
- NORM.S.INV()/NORMSINV()
- PEARSON()
- PERCENTILE()
- PERCENTILE.EXC()
- PERCENTILE.INC()
- PERCENTRANK()
- PERCENTRANK.EXC()
- PERCENTRANK.INC()
- PERMUT()
- POISSON.DIST()/POISSON()
- PROB()
- QUARTILE()
- QUARTILE.EXC()
- QUARTILE.INC()
- RANK()
- RANK.AVG()
- RANK.EQ()
- RSQ()
- SKEW()
- SLOPE()
- SMALL()
- STANDARDIZE()
- STDEV.P()/STDEVP()
- STDEV.S()/STDEV()
- STDEVA()
- STDEVPA()
- STEYX()
- T.DIST()
- T.DIST.RT()
- T.DIST.2T()/TDIST()
- T.INV()
- T.INV.2T()/TINV()
- T.TEST()/TTEST()
- TREND()
- TRIMMEAN()
- VAR.P()/VARP()
- VAR.S()/VAR()
- VARA()
- VARPA()
- WEIBULL.DIST()/WEIBULL()
- Z.TEST()/ZTEST()
- 13. Database Functions
- 14. Cube Functions
- 15. Financial Functions
- ACCRINT()
- ACCRINTM()
- AMORDEGRC()
- AMORLINC()
- COUPDAYBS()
- COUPDAYS()
- COUPDAYSNC()
- COUPNCD()
- COUPNUM()
- COUPPCD()
- CUMIPMT()
- CUMPRINC()
- DB()
- DDB()
- DISC()
- DOLLARDE()
- DOLLARFR()
- DURATION()
- EFFECT()
- FV()
- FVSCHEDULE()
- INTRATE()
- IPMT()
- IRR()
- ISPMT()
- MDURATION()
- MIRR()
- NOMINAL()
- NPER()
- NPV()
- ODDFPRICE()
- ODDFYIELD()
- ODDLPRICE()
- ODDLYIELD()
- PMT()
- PPMT()
- PRICE()
- PRICEDISC()
- PRICEMAT()
- PV()
- RATE()
- RECEIVED()
- SLN()
- SYD()
- TBILLEQ()
- TBILLPRICE()
- TBILLYIELD()
- VDB()
- XIRR()
- XNPV()
- YIELD()
- YIELDDISC()
- YIELDMAT()
- 16. Mathematical and Trigonometry Functions
- Functions for Mathematical Calculations
- Functions for Trigonometry Calculations
- Other Functions
- ABS()
- ACOS()
- ACOSH()
- AGGREGATE()
- ASIN()
- ASINH()
- ATAN()
- ATAN2()
- ATANH()
- CEILING()
- CEILING.PRECISE()
- COMBIN()
- COS()
- COSH()
- DEGREES()
- EVEN()
- EXP()
- FACT()
- FACTDOUBLE()
- FLOOR()
- FLOOR.PRECISE()
- GCD()
- INT()
- LCM()
- LN()
- LOG()
- LOG10()
- MDETERM()
- MINVERSE()
- MMULT()
- MOD()
- MROUND()
- MULTINOMIAL()
- ODD()
- PI()
- POWER()
- PRODUCT()
- QUOTIENT()
- RADIANS()
- RAND()
- RANDBETWEEN()
- ROMAN()
- ROUND()
- ROUNDDOWN()
- ROUNDUP()
- SERIESSUM()
- SIGN()
- SIN()
- SINH()
- SQRT()
- SQRTPI()
- SUBTOTAL()
- SUM()
- SUMIF()
- SUMIFS()
- SUMPRODUCT()
- SUMSQ()
- SUMX2MY2()
- SUMX2PY2()
- SUMXMY2()
- TAN()
- TANH()
- TRUNC()
- 17. Engineering Functions
- A. Excel Functions (in Alphabetical Order)
- B. Excel Functions (by Category)
- C. What’s New in Excel 2007 and Excel 2010
- D. About the Authors
- Index
- About the Authors
- Copyright
Syntax. EOMONTH(start_date,months)
Definition. This function returns the serial number of the last day of the month that is the indicated number of months before or after the start date.
Arguments
start_date (required). The date that represents the start of the calculation.
months (required). The number of months before or after the start date. A positive value for the months returns a future date, and a negative value returns a past date.
Background. This function is mostly used for accounting and banking purposes. The EOMONTH() function can be used to calculate a repayment date and due date on the last day of a month.
Alternatively, you can use the YEAR() and MONTH() functions to divide the start date into three parts. Increase the resulting value of months by 1 and then reconstruct the three component parts with the DATE() function to get a regular date (serial number). Subtract one day from this date to obtain the last day of the month.
=DATE(YEAR(start_date),MONTH(start_date)+months+1,1)-1
Problems might occur if you enter dates as text. If a month isn’t an integer, the decimal places are truncated. If the start date isn’t a valid date, the EOMONTH() function returns the #NUMBER! error. If the sum of the start date and months is an invalid date, the function returns the #NUMBER! error.
See Also
For more information about saving dates in Excel, see the section titled The Excel Date System in Chapter 2.
Example. Assume that you want to designate the last day of the month as the due date for a credit period 18 months from January 1, 2010. The formula
=EOMONTH("01/01/2010",18)returns 07/31/2011 as the due date (see Figure 7-4).
See Also
DATE(), DATEDIF(), DAYS360(), EDATE(), NETWORKDAYS(), NETWORKDAYS.INTL(), WORKDAY(), WORKDAY.INTL()
Sample Files
Use the EOMONTH worksheet in the Date_Time.xls or Date_Time.xlsx sample file. The sample files are found in the Chapter07 folder. For more information about the sample files, see the section titled Using the Sample Files.
-
No Comment