MONTH()
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. MONTH(serial_number)
Definition. This function extracts the month number from a date. The calculated month numbers are in the range 1 (January) through 12 (December).
Arguments
serial_number (required). The date for which the month number is calculated.
Background. With this function, as well as with the YEAR() and DAY()functions, you can extract a valid date and use the components for other calculations. Use the MONTH() function to group values by month. Note that problems might occur if you enter dates as text. The values returned by the YEAR(), MONTH(), and DAY() functions correspond to the Gregorian calendar.
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 enable the Excel AutoFilter for the months and need to calculate the month values from a date column by using an auxiliary column. To do this, use the formula
=MONTH(date_value)
Here are more examples:
Sample Files
Use the MONTH 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
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.