tailieunhanh - formulas and functions with microsoft excel 2003 phần 4

(text, old_text, văn bản_mới, instance_num) văn bản: văn bản hoặc tham chiếu đến một ô chứa văn bản trong đó nhân vật được thay thế. old_text: Văn bản đó phải được thay thế. văn bản_mới: Tiêu đề thay thế old_text. instance_num: | Use the DATE function to add months to a date Let s say we want to add a number of months to a given start date. In a new worksheet list different start dates in column A. In column B enter the number of months to be added to or subtracted from the start date. Based on that data the end date can be calculated. To add months to or subtract months from dates 1. In cells A2 A10 list some start dates as shown in Figure 4-15. 2. List in cells B2 B10 the number of months to add or subtract. 3. Select cells C2 C10 and type the following formula DATE YEAR A2 MONTH A2 B2 DAY A2 . 4. Press Ctrl Enter . C2 - -DATE YEAR A2 MONTH A2 B2 DAY A2 A B c D E 1 start month end 2 4 14 2005 2 6 14 2005 3 11 21 2005 5 4 21 2006 4 2 2 2005 -2 12 2 2004 5 12 21 2005 9 9 21 2006 6 1 1 2005 7 8 1 2005 7 9 8 2005 -5 4 8 2005 8 12 31 2005 1 1 31 2006 9 2 27 2005 0 2 27 2005 10 3 30 2005 7 11 Figure 4-15 Note To determine an end date in the past put a minus sign in front of the number of months. 106 Chapter 4 Use the EOMONTH function to determine the last day of a month To find the last day of a month use the EOMONTH function EOMONTH st rt_d te offset_monthsỴ . This function returns the date of the last day of the month offset_months from start_date. If the function is not available load the Analysis ToolPak add-in. Select Add-Ins. from the Tools menu bar and select Analysis ToolPak. To determine the last day of a month 1. In cells A2 A10 enter some dates. 2. In cells B2 B10 enter the desired offset from the start date positive or negative values . 3. Select cells C2 C10 and type the following formula EOMONTH A2 B2 . 4. Press Ctrl Enter . C2 fe EOMONTH A2 B2 A B c D 1 date offset end of month 2 6 9 2005 5 11 30 2005 3 11 21 2005 -2 9 30 2005 4 1 29 2005 3 4 30 2005 5 7 18 2005 -1 6 30 2005 6 12 25 2005 -3 9 30 2005 7 2 21 2005 2 4 30 2005 s 12 2972005 3 3 31 2006 9 11 21 2005 0 11 30 2005 10 1 2 2005 1 2 28 2005 11 Figure 4-16 Date and Time Functions 107 Use the DAYS360 function to calculate .