Excel: 11 Using Everyday Functions: Math, Date, and Time and Text Functions

Examples of Text Functions

A. Joining Text with the Ampersand (&) Operator =A2&" "&B2 to add a space in between your text strings in column A and B the ampersand character allows you add more items than 30. =CONCATENATE (text1, text2) you can join text up to 30 items (text strings, numbers, single-cell references) =CONCATENATE (A2, " ", B2) if you want to add a space in between. This function runs like the joining text as stated above. Because the concatenate formula only allows the user to add a single-cell reference, you cannot input a range. B. Using LOWER, UPPPER, or PROPER to Convert Text Case Coverts text to and from capital letters =LOWER (text) converts text to lowercase. This can be a reference or a text string. =UPPER (text) Converts text to uppercase. This can be a reference or a text string. =PROPER (text) converts text to capitalize the first letter (text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text). However, it does not correctly capitalize names with apostrophes, interior, creating company nicknames (Ibm, Ae, 3m). C. Using TRIM to Remove Trailing Spaces Trim removes leading and trailing spaces from importing data for utilizing VLOOKUP and MATCH (). Edit the cell with the edit mode then look at the flashing cursor to remove all the extra unneeded spaces. TRIM formula and copy and paste value =VLOOKUP(TRIM(A2), $F$2:$G$5, 2, FALSE) =VLOOKUP(A2, TRIM (F$2:G$5), 2, FALSE) D. Using CLEAN to Remove Nonprintable Characters from Text =CLEAN (text) to remove some low-level computer code before the proliferation of web queries, oracle, and SAP. Text-can be a cell reference that has imported data E. Using the CHAR function to Generate Any Character You have browse in your PC desktop for the Wingdings character set: Start, All Programs, Accessories, System Tools, Character Map =CHAR(number) returns the character specified by a number from 1-255 F. Using the CODE Function to Learn the Character Number for Any Character =CODE (text) Returns the numeric value from a character in a text string G. Using LEFT, MID, or RIGHT to Split Text =LEFT (text, num_chars) returns the first character in a text string based on the number of characters specified =RIGHT (text, num_chars) returns characters in a text string based on the number of characters specified. =MID (text, start_num, num_chars) returns a specific number of charcters from a text string, starting at the position specified based on the number of characters specified. Start_num-this is the position of the first character you want to extract in a text Num_chars-this specifies the number of characters you want to extract in a text from the starting position H. Using LEN to Find the Number of Characters in a Text Cell =LEN() determines the length of characters in a cell, including any leading or trailing spaces. The LEN can be used with the RIGHT, LEFT and MID functions =MID(A2, 5, LEN (A2)-4)) I. Using SEARCH or FIND to Locate Characters in a Particular Cell =SEARCH (find_text, within_text, start_num) to find a cell exactly as the text string. ? finds a single character * finds any number of characters ~ to find an actual ? or * ~~ to find an actual ~ returns the number of the character at which a specific character or text string is first found, beginning with start_num. to determine the location of a character or text string whinthin another text string so you can use MID or REPLACE functions =FIND(find_text, within_text, start_num) Does not distinguish uppercase and lowercase letters Finds one text string within another text string and returns the number of the starting position. J. Using SUBSTITUTE and REPLACE to Replace Characters =SUBSTITUTE (text, old_text, new_text, instance_num) you can replace specific text in a text string text-contains text that you want to replace old_text-this is the text that you will be replacing new_text- This is text that you will be replacing the old_text with instance_num-if you specified, then only that instance of old_text is replaced (1). Otherwise every occurrence is replaced. =SUBSTITUTE ("Sales Data", "Sales", "Cost") is "Cost Data" =REPLACE (old_text, start_num, num_chars, new_text) replace any text that occurs in a specific location in a text string, based on the number of characters specified with a different text string. Start_num-the position of the character in old_text that you want to replace with new_text Num_chars-this is the number of characters in old_text that you want replace with new_text New_text- the text that will replace the old text. K. Using REPT to Repeat Text Multiple Times =REPT (text, number_times) repeats text in a number given of times. Cannot be longer than 32,767 characters =REPT("Hello",3) is Hello, Hello, Hello =B2&REPT(".", 45 - (LEN(A2) + LEN(B2)))&A2 L. Using EXACT to Test Case =EXACT (text1, text 2) function compares two text strings and returns TRUE if they are exactly the same. Is case sensitive but ignores formatting. M. Using TEXT, DOLLAR, and FIXED to Format a Number as Text =TEXT(value, format_text) =TEXT(A12, "m/d/y") to force the date to display in this format press ctrl + 1 for the format dialog , custom category to create a format =DOLLAR (number, decimals) converts a number to currency format =DOLLAR(A3, 2) in two decimal places =FIXED(number, [decimals], [no commas]) rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the results as text. =FIXED(A7, 0, TRUE) is 1235 N. Using the T and VALUE Functions =T("text") returns the value in the cell only if it is text =VALUE (text) Converts a text string that represents a number/ date to a number/ date O. Using Functions for Non-English Character Sets Functions deal with text in character systems where each character takes up than 1 byte. This is true for Asian languages.

Examples of Math Functions

A. Using SUM to Add Numbers =SUM(number1,...) You can add numbers from one or more ranges of data up to 255 ranges and arguments =SUM((A10,A12), (A14,16)) as 2 arguments =SUM(Jan:Dec!B20) Adds the sum of the B20 cells from all of the 12 sheets =SUM('Jan 2011:Dec 2011'!B20) If the sheet names contain non-alphabetical characters or contain spaces, insert apostrophes =SUM(F13:H14 G12:G15) These are two ranges without a comma in between To quickly enter a SUM formula, you can press Alt+= B. Using AGGREGATE to Ignore Error Cells or Filtered Rows To add numbers while ignoring error cells, use the new ARREGATE function Perform 17 functions on a range of data while selectively ignoring error cells and/or rows hidden by a filter. =AGGREGATE(function_num, options, array, [k]) =AGGREGATE(function_num, options, ref1, ref2,...) With the options argument, you can choose to ignore any, all or none of: Other SUBTOTAL (add 0), Hidden rows (add 1), Error values (add 2), All (3), OR to include SUBTOTALS (add 4) OR to ignore error values but include other SUBTOTAL values (add 5) Arguments for the AGGREGATE functions is in table 11.4 Functions Available in AGGREGATE: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDDEV.S, STDDEV.P, SUM, VAR.S, VAR.P & MEDIAN, MODE.SNGL, LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE. EXC To have a total sum of hidden and visible rows, use the SUM function C. Using COUNT or COUNTA to Count Numbers or Nonblank Cells =COUNT (value1, value2,...) counts all the numeric or date cells in a range or array, =COUNTA (value1, value2,...) counts all text, error, the nonblank cells in a range or array a. Choosing Between COUNT AND COUNTA As stated above. D. Using ROUND, ROUNDDOWN, ROUNDUP, INT, TRUNC, FLOOR, FLOOR.PRECISE, CEILING, CEILING.PRECISE, EVEN, ODD, or MROUND to Remove Decimals or Round Numbers All of these functions can be used to round a result or remove decimals from a result =TRUNC(truncates)(number) removes the decimals from a result =TRUNC (-1.9) is -1 =TRUNC (1.9) is 1 =INT (number) to remove the decimals from a result and always round down to the next lowest integer. For a negative number, INT rounds away from zero to produce the next lowest integer =INT(-1.1) is -2 =EVEN (number) rounds away from zero to the next even integer =EVEN (-3) is -4 =EVEN (3) is 4 =ODD (number) rounds away from zero to the next odd integer =ODD (1.1) is 3 =ODD (-3.1) is -5 TRUNC, INT, EVEN, ODD functions always change a number to an integer. =ROUND (number, num_digits) =ROUND ( -1.49999,0) = -1 =ROUND (-1.5,0) = -2 =ROUNDUP (number, num_digits) =ROUNDUP (1.01, 0) = 2 =ROUNDUP (-1.01,0) = -2 =ROUNDDOWN (number, num_digits) =ROUNDDOWN (-19.999,0) = -19 =ROUNDDOWN (1.999, 0) = 1 Round a number to a specified number of decimal places. They all take the following arguments. number: This is the number you want to round num_digits: This specifies the number of digits to which you want to round number =MROUND (number, multiple) to round to the nearest multiple. =MROUND (C2, 5), C2= 185.9375, is 185 =CEILING (number, significance) rounds a number to the next multiple. =CEILING (-2.5,-1) is -3 =CEILING. PRECISE (number, [signficance]) rounds up =CEILING.PRECISE (-2.5, -1) is -2 =CEILING.PRECISE (2.5, 1) is 3 =FLOOR (number, significance) rounds the number to next lowest multiple =FLOOR.PRECISE (number, [significance]) number- This is the number you want to round multiple/ significance- This is the nearest multiple that you want to round toward. Note, that if the number is negative, multiple or significance must also be negative. E. Using SUBTOTAL Instead of SUM with Multiple Levels of Totals =SUBTOTAL (function_num, ref1, ref2,...) function_num-This is a number from 1 to 11. The most common function number is the number 9, which (for no apparent logical reason) is used to sum. AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP Always ignore rows as result of a filter. Add 100 to the function num to prevent from including rows hidden by using the HIDE command. ex. 9 to include hidden value, 109 to ignore hidden value, function is sum Ref1,..- These are up to 254 ranges or references that you want to subtotal. Unlike with SUM, the references in a SUBTOTAL function cannot be 3D references Tip: Any other nested subtotals in the range are ignored to prevent double counting Please use SUBTOTAL instead of SUM for the customer totals, the problem of creating a grand total becomes simple table 11.6 for more guidance Any other nested subtotals in the range are ignored to prevent double counting Refer to Table 11.6 This is a great feature for the ad hoc reporting F. Using SUBTOTAL Instead of SUM to Ignore Rows Hidden by a Filter The SUBTOTAL function to reflect the total of the visible rows G. Using RAND and RANDBETWEEN to Generate Random Numbers and Data a. Choosing a Random Item from a List =RAND() to generate a random decimal between 0 and .999999 but don't want the numbers to change every time the cell is calculated Press F9 to change the formula to a random number For a number between 0 and 100, use the =RAND()*100 convert the formulas to values by turning off the automatic calculation (located on Formulas, Calculation Options, Manual)! =RANDBETWEEN (bottom, top) To generate a random integer as specified with the smallest to the largest integer of the dataset. This generates whenever the cell is calculated. =RANDBETWEEN (50, 59) =RANDBETWEEN (5000,9900)/1000 can generate prices between $50 and $99 =CHAR(RANDBETWEEN(65,90)) to generate random capitalized letters from A-Z. =INT (RAND() * 10) + 50 H. Choosing a Random Item from a List =INDEX ($E$2:$E$6, RANDBETWEEN (91,5)) To randomly assign employees to certain projects, this is a double drop down handle in Column B. Column A: Projects Column B: Assigned to Column E: Name of the employees =ROMAN (number, form) convert numbers from Arabic to Roman numerals number-this is the arabic numeral you want to converted form-this is a number that specifies the type of Roman numeral you want. The Roman numeral style ranges from Classic to Simplified, becoming more concise as the value of form increases =ROMAN (YEAR (NOW())) You would be converting today's year to roman numeric value I. Using ABS() to Figure Out the Magnitude of ERROR =ABS(number) measures the size of the error in an absolute value, ignoring the sign. =ABS(G4-H4) to demonstrate that the other station's forecast is off by 20 degrees on average J. Using PI to Calculate Cake or Pizza Pricing =PI() The PI function returns the number 3.14..., the mathematical constant, accurate to 15 digits. =PI()*(B7/2) ^ 2 To calculate the constant PI, with the radius (Half of the diameter) to the second power When calculating product (round things) costs, you may want to use this function K. Using =COMBIN to Figure out Lottery Probability =COBIN (combinations) (number, number_chosen) Returns the number of combinations for a given number of items. To determine the total possible number of groups for a given number of items. =COMBIN (48, 6) is 11.1 million combinations figure out the number of combinations (6) out of a pool of lottery systems (48). L. Using FACT to Calculate the Permutation of a Number =FACT (v_number) Returns the factorial of a number (how many different ways you can arrange the variable), which is positive number. The factorial of a number is equal to 1 x 2 x 3 x to V number =FACT(5) is 1 x 2 x 3 x 4 x 5 is 120 =FACT(0) is 1 =FACTDOUBLE (number) Multiplies every other number. =FACTDOUBLE (8) = 8*6*4*2 =FACTDOUBLE (9)=9*7*5*3*1 M. Using GCD and LCN to Perform Seventh-Grade Math Least Common Multiple =LCM (number1, number2,...) The least common multiple of integers, which are the smallest positive integer that is a multiples of all integer arguments. To add fractions with different denominators The least common denominator of 24 and 36 is 72. For example: 72/ 24 and 36. 72/ 24 = 3 72/36 = 2 The denominator has to be the same for both of the integers. Greatest Common Denominator =GCD(number1, number2,...) The number1 arguments are from 1 to 29. The greatest common divisor of 24 and 36 is 12 without a remainder. 24/12 = 2 36/12 = 3 N. Using MULTINOMIAL to Solve a Coin Problem =MULTINOMIAL (number1, number2,...) returns the ratio of the factorial of a sum of values to the product of the factorials. =MULTINOMIAL (a, b, c, d) =(a+b+C+d)!/ a!xb!xc!xd! The factorial of (Dividers + Coins) % Factorial of Coins x Factorial of Dividers. The values are between 1 to 255 =MULTINOMIAL (3,6) performs the calculation (3 + 6)!/ 3! x 6! O. Using MOD to Find the Remainder Portion of a Division Problem =MOD(number, divisor) to break a group to a subgroup divides one number by another and reports back just the remainder portion of the result. returns the remainder after a number is divided by the divisor. The result has the same sign as divisor. number- the denominator divisor-a multiple for dividing the numerator A gallery contains 84 icons arranged with 4 icons per row. To find the column for the 38th icon use =MOD (38, 4) =MOD(Row(),4) Assigns all employees to one of four groups by organizing them into the groups, after the formula, paste the values and sort by the remainders. P. Using QUOTIENT to Isolate the Integer Portion in a Division Problem =QUOTIENT (numerator, denominator) returns the whole number and ignores the remainder This function is great for calculating full cases of products If one case of work is 4 items produced and the worker produced 43 items in a shift, then how many complete cases did the worker produced? =QUOTIENT (43,4) is 10 numerator- this is the dividend denominator-this is the divisor Is more accurate than INT when the result is negative Ex. QUOTIENT (5, -4) is -1 INT (5/-4) is -2 Tip: if you are a fan using the INT to simulate the QUOTIENT, consider using the RUNC(), or ISO>CEILING () instead. Q. Using PRODUCT to Multiply Numbers =PRODUCT (number1, number2,...) Multiples a range of numbers or cells up from 1 to 255 characters by each other =PRODUCT(2,2) =PRODUCT(A2:A50) To calculate hold down Ctrl + Shift while pressing Enter R. Using SQRT and POWER to Calculate Square Roots and Exponents =SQRT (number) =SQRT (8) 2.828 The number does not compute a negative integer =POWER (number, power) =POWER (6, 5) six to the fifth power 6 x 6 x 6 x 6 x 6 a. Figuring Out Other Roots and Powers For roots, you can raise a number to a factional power: 6^3 is six raised to the third power 256 ^ (1/8) is the eighth root of 256. This is 2 125 ^ (1/3) is the third root of 125. This is 5. S. Using SIGN to Determine the Sign of a Number =SIGN(number) reports whether the sign is positive (1), negative (-1) or zero (0). You can use this function with the MROUND This function is in the information and math functions T. Using COUNTIF, AVERAGEIF, and SUMIF to Conditionally Count, Average or Sum Data =COUNTIF (range, criteria) counts the number of cells within a range that meet the given criteria. This function takes the following arguments: range-This is the range of cells from which you want to count cells. criteria-This is the criteria in the form of a number, an expression, or text that defines which cells will be counted. You can use wildcard characters (?) to match any single character or (*) to match any sequence of characters in the criteria. If find either (?) or (*) type (~) before the character. You have a database that contains thousands of records, Your goal is to find out how many records came from each region. =COUNTIF($C$11:$C$5011, "East") =COUNTIF($C$11:$C$5011, A2) would ask for a range of data and then the value to look for in that range. =SUMIF(range, criteria, sum_range) adds the argument specified by a given criteria asks for a range of data, the value to look for in that range, and then another range of data to be summed when a match is found range-this is the range of cells you want evaluated criteria-this is the criteria in the form of a number, an expression, or text that defines which cells will be counted sum_range-This is the range of cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed. =SUMIF($C$11:$C$5011, A2, $H$11) =SUMIF(H11:H5011, ">100000) =AVERAGEIF(range, criteria, average_range) range-this is the range of cells you want evaluated criteria-this is the criteria in the form of a number, an expression, or text that defines which cells will be counted average_range-if the cells in the average_range are empty or contain text or TRUE/FALSE, they are ignored in the calculation of average =AVERAGEIF($C$11:$C$5011, A2, $I$11) averages the cells specified by a given criteria Occasionally, the range you want to search is also the range to sum U. Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS() To signify that multiple IFS are being considered Can handle up to 127 conditions for a certain region and product Specify the range to be summed or averaged, then specify pairs of arguments. In each pair, you first specify the range to check and then the value to match in that range. =SUMIFS(sum_range, criteria_range1, criteria1[,criteria_range2, criteria2...]) adds the cells in a range that meet multiples criteria. Sum_range-is the range to sum Criteria_range1, are more ranges in which to evaluate the associated criteria Criteria1, criteria2 are more criteria in the form of a number, and expression, a cell reference, or text that define which cells will be added. Ex. 32, "32", ">32", "apples" or B4 Sum_range-is summed only if all the corresponding criteria specified are true for that cell. If the cells contain TRUE evaluate to 1; cells in sum_range that contain FALSE evaluate to 0. You can use wildcard characters (?) to match any single character or (*) to match any sequence of characters in the criteria. If find either (?) or (*) type (~) before the character. The size and shape of each criteria_range an sum_range must be the same. =AVERAGEIFS(average_range, criteria_range1, criteria1[, criteria_range2, criteria2...]) Returns the average (arithmetic mean) of all cells that meet multiple criteria. The arguments are the same as SUMIFS() =COUNTIFS(range1, criteira1[,range2,criteria2...]) counts the number of cells in a range that meet multiple criteria. There is no need to specify sum_range. The arguments consist of pairs specifying criteria. The first argument in each pair specifies a criteria region. The second argument in each pair specifies the criteria value to match

Reference

Alphabetical List of Math Functions, Date and Time Functions, Text Functions

Examples of Date and Time Function

Ctrl + : enters the current cell Ctrl + ; enters the current date in a cell Ctrl + : enters the current cell Ctrl + ; enters the current date in a cell A. Using NOW and TODAY to Calculate the Current Data and Time or Current Data =NOW (): Returns the serial number of the current date and time. To display a date without a calculation =TODAY () Returns the serial number of the current date. The serial number is the date/ time code that Microsoft Excel uses for date and time calculations. To display a date without a calculation B. Using YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND to Break a Date/ Time Apart =YEAR(serial_number): Returns the year corresponding to a date. The year is returned as an integer in the range 1900 through 9999. =MONTH (serial_number) =DAY (serial_number) =HOUR (serial_number) =MINTUE (serial_number) =SECOND (serial number): Returns the seconds of a time value. The seconds are given as an integer in the range 0 to 59. You can decode a formatted cell of date and time in hour, minute and second by: A1 = 07/14/11 7:18:29PM =YEAR(A1) returns a four digit number =MONTH(A1) returns 1-12 =DAY(A1) returns 1-31 =HOUR(A1) returns 1-24 =MINUTE(A1) returns 1-60 =SECOND(A1) returns 1-60 C. Using DATE to Calculate a Date from Year, Month, and Day =DATE (year, month, day) year - can be one to four digits =DATE (100, 1, 2) as January 2, 2000 (1900 + 100) month - if the month is greater than 12, then it is calculated correctly. =DATE(2000, 1, 2) returns the serial number representing February 2, 1999. day - if the days are greater than the month specified, then it calculates as well as the month =DATE(1988, 1, 35) returns February 4, 1988 To calculate the end of the month: =DATE (Year (A2), MONTH (A2) + 1,1)-1 D. Using TIME to Calculate a Time =TIME (hour, minute, second): Returns the decimal number for a particular time. The decimal number returned by TIME is a value ranging from 0 to 0.999999999, representing the times from 0:00:00 (12:00:00 a.m.) to 23:59:59 (11:59:59 p.m.) =TIME(12, 72, 120) is 1:14PM E. Using DATEVALUE to Convert Text Dates to Real Dates =DATEVALUE (date_text) convert text entries (text dates) to serial numbers January 21, 2011 must have a space after the comma to easily convert F. Using TIMEVALUE to Convert Text Times to Real Times =TIMEVALUE (time_text): Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 to 0.999999, representing the times from 0:00:00 (12:00:00 a.m.) to 23:59:59 (11:59:59 p.m.) G. Using WEEKDAY to Group Dates by Date of the Week =WEEKDAY (serial_number, return_type): Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (for Sunday) to 7 (for Saturday), by default. Serial number-this is the days after January 1 1900 as explained earlier. Return_type-1 or omitted, the weekday starts with Sunday, 2 starts with Monday, 3 starts with Monday in groups, converts the date to Monday. H. Using WEEKNUM to Group Dates Into Weeks =WEEKNUM (serial_num, return_type): Returns a number that indicates where the week falls numerically within a year serial number- is the date within the week return_type-determines on what day the week begins. 2 if it starts on a Monday I. Alternate Calendar Systems and DAYS360 4-4-5 calendar-One you have 5 weeks, then it is outputted to Month 2. 52 7-day weeks- you can compare year to year because the weekday is easy to compare 360 day calendar-excel utilized this type of calendar =DAY360 (start_date, end_date, method) start_date, end_date-The start date can occur after the end date, the end result will have a negative sign number. Dates can be entered as text strings within quotation marks ("1/30/1998") as serial numbers (35825), or as results of other formulas or functions (DATEVALUE ("1/30/1998")). Method-whether to use US (FALSE) or European (TRUE) method J. Using YEARFRAC or DATEDIF to Calculate Elapsed Time =YEARFRAC (start_date, end-date, basis): Calculates the fraction of the year represented by the number of whole days between two dates (start_date and end_date). You use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term. Start_date and end_date is the same as the Day 360 arguments. Basis-type of day count basis to use; basis is 0 or omitted uses 30/360 plan =DATEIF(start_date, end-date, unit) calculates the complete years, months, and days between two dates. Unit-this is the type of information you would like to return. Please refer to table 11.7 for the unit values: K. Using EDATE to Calculate Loan or Investment Maturity Dates =EDATE (start_date, months) to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. Months- a positive number yields to a future month in contrast a negative number yields to a past month. L. Using EOMONTH to Calculate the End of the Month =EMONTH (start_date, months): returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Converts any date to the end of the month. You have format the cell to EDATE to see the result Start_date- this is a serial number, formula, text string within quotations Months-a negative number is a past date and a positive number is a further date. To calculate maturity dates or due dates that fall on the last day of the month M. Using WORKDAY or NETWORKDAYS to Calculate Workdays =WORKDAY (start_date, days, holidays) Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. To view the number as a date, format the cell as a date. =NETWORKINGDAYS (start_date, end_date, holidays): to calculate employee benefits that accure based on the number of days worked during a specific term. working days exclude weekends and any dates identified in holidays. To embed holidays in the function: In the formula, press the F9 key to convert the cell references to serial numbers , then you can delete the range of the holiday dates. N. Using International Versions of WORKDAY or NETWORKDAYS The international versions still require a consecutive two-day weekend =WORKDAY. INT (start_date, days, weekend, holidays): Returns a number that represents a date that is the indicated number of working dates before or after a starting date. To accommodate calendar systems where the weekend is a pair of days other than Saturday and Sunday. Extends the functionality of WORKDAY to calendars in which the weekend is a pair of days other than Saturday and Sunday. =NETWORKDAYS. INT (start_date, end_date, weekend, holidays): Returns the number of whole working days between start date and end date. To support calendars in which the weekend is a pair of days other than Saturday and Sunday. Extends the functionality of NETWORKDAYS to companies in which the weekend is a pair of days other than Saturday and Sunday. The values of the weekend argument fall into 1-17 categories: 1-weekend on Saturday and Sunday 2-weekend on Sunday and Monday and so on 11-Sunday only 17-Saturday only

Dates and Times in Excel

Stores dates as the number of days since January 1, 1900. June 30, 2011 is 40724 days after 1/1/1900 Correctly format the cell before adding 40359.625 is a serial number for June 30, 2010 for 3pm After the decimal point, the time is calculated. 6AM - .25 12PM - 0.5 6PM - 0.75 A. Understanding Excel Date and Time Formats Mm-displays the month with two digits M-displays the month with one or two digits mmm-displays a three letter abbreviation mmmm-spells out the month mmmmm-first letter of the month dd-displays the day of the month d-displays the day of themonth with one or two digits ddd-displays a three-letter abbreviation for the name of the weekday dddd-spells out the name of the weekay yy or y-uses two digits for the year yyyy or yyy-uses four digits for the year Press Ctrl + 1 Custom time format codes are listed in figure 11.36 To display date and time, you enter the custom date format code, a space, and then the time format code.