Hello all Forwarding a post which did not get to the list. Harish Kotian Quoting:
-----Original Message----- From: Minar Singh [mailto:[email protected]] Sent: 29 January 2012 21:09 Dates and Date Functions in Microsoft Office Excel By James F Pearson Microsoft Office Excel stores dates as numbers called serial numbers or serial values and dates can be displayed (formatted) in various ways. Being numbers, dates can be sorted or used in calculations and there are several Excel worksheet functions that can be used with dates. This article explains dates, date formatting and date functions in Excel, and includes a few tips about using the fill handle. Excel supports two date systems. The 1900 date system is the default on computers running Windows operating systems and the earliest date (serial value 1) in that system is January 1, 1900. The 1904 date system is the default on Apple Macs where date serial number 1 is January 2, 1904. The date system can be changed in Excel Options, but that's not really necessary because Excel automatically adjusts the dates if a file created in Excel for Windows is opened on a Mac or vice versa. There is an error in the 1900 date system. Excel considers 1900 to be a leap year, but it's not, so there is a February 29 in 1900 in Excel for Windows. That means weekdays displayed for dates from February 1 to 29, 1900 and calculations using dates before March 1, 1900 are out by a day. That probably won't bother you, but keep it in mind. When a date is entered into a cell with a two digit year, e.g. 9/5/11, Excel interprets the century. If the year is 00 to 29 Excel assumes it is 2000 to 2029. If the year is 30 to 99 Excel assumes it is 1930 to 1999. That can be over-ridden by entering a date with a four digit year, e.g. 9/5/1911. The way Excel interprets centuries can be changed in Control Panel, Regional and Language Options, Regional Options, then click on the Customize button and select the Date tab.em Cells can be formatted to display dates in various ways by right clicking the cell, selecting Format Cells from the pop-up menu, then selecting Date on the Number tab. The date formats that can be selected in the "Type" field are determined by the country selected in Control Panel, Regional and Language Options, but can be over-ridden by changing the "Locale (location:)" field on the Format Cells dialogue box, e.g. from English (Australia) to English (US). For example, if 9/5/11 is entered into a cell in English (Australia) format (where dates are shown as day, month, year) it could be formatted as 9/5/11, 9/05/2011, 09-May-11 or Monday, 9 May 2011. In the US, where month, day, year format is used, the same date entered as 5/9/11 could be formatted as 05/09/11, 5/9/2011, May 9, 2011, or 9-May-2011. Alternatively, a date can be displayed as a number by formatting the cell as a number without decimal places (May 5, 2011 as a number is serial value 40672 in Excel for Windows). Dates can also be custom formatted. For example, the above date custom formatted as "ddd" would display in the cell as Mon or if it was custom formatted as "dddd" it would display as Monday. It could also be custom formatted as "mmm" to display May or as "mmmm, yyyy" to display May, 2011. The following date functions (each shown with its syntax and a brief explanation) can be used in Excel. It might be easier to follow the examples below on a worksheet, so to start with, on a new (blank) worksheet, enter 2011 into cell A1, 5 into cell A2 and 9 into cell A3 -- and when copying the formulas below, make sure you copy from the = sign to the last closed bracket. The date serial values mentioned below relate to Excel for Windows. In the 1904 date system on the Mac they will be different. DATE (year, month, day) returns the serial number that represents a date. For example, copy the formula =DATE(A1,A2,A3) and paste it into cell A4 on your example worksheet. It will return 40672, which is the serial value for May 5, 2011 and that date will be displayed in default date format in the cell. Numbers and nesting can be included in this function to add a number of years, months or days to a date. Copy =DATE(YEAR(A4),MONTH(A4)+18,DAY(A4)) into cell A5 on your worksheet. It will add 18 months to the date in cell A4 and return 41222, which is the serial value for November 5, 2012. Now enter the formula =A5-A4 into cell A6. It will display 550, which is the number of days between May 5, 2011 and November 5, 2012. DATEVALUE (date_text) converts a date in the form of text to a number. Format cell A7 as text then enter 09-May-11 into that cell. Then copy =DATEVALUE(A7) into cell A8. It will return the serial value 40672, which as you read above, is the date value for that date. The next three functions (with 40672 still showing in cell A8) will show that is so. DAY (serial-number) returns the day (1 to 31) from a date, so if =DAY(A8) is entered into cell A9, it will return the day, 9. MONTH (serial-number) returns the month (1 to 12) from a date, so =MONTH(A8) entered into cell A10 will return the month, 5. YEAR (serial-number) returns the year (1900 to 9999) from a date, so =YEAR(A8) entered into cell A11 will return the year, 2011. TODAY is a simple function (with no arguments) that enters today's date into a cell. It updates every day to change the date in the cell to the current day's date (which is actually the date set on your computer). Enter =TODAY() into cell A13 on your example worksheet (yes, we left cell A12 blank) and if the function doesn't display today's date correctly, adjust the date on your computer. Now, let's set up your worksheet for the next function. Enter your date of birth into cell A12. DATEDIF (first_date,second_date,return_type) returns the difference between two dates in a variety of different ways as specified in the return_type. Return-type is the letter "d", "m", "y" or a combination thereof (entered into the function with the quotation marks, or entered into a cell without quotation marks if the function will refer to the cell(s) in the return_type argument -- see example below). Although DATEDIF has been available as a function in Excel for 15 or more years, it is not included in Excel Help documentation, is not included in the drop-down list of Date and Time functions on the Insert Function dialogue box and can't be accessed by clicking on the fx button. To use DATEDIF it is therefore necessary to type the function directly into a cell -- but knowing how to use it can be handy. Copy the formula =DATEDIF(A12,A13,"d") and paste it into cell A14. The first_date is your date of birth, the second_date is today's date and the result shown in cell A14 is the number of days between those two dates. The formula =A13-A12 would have produced the same result. Now type the letter d (without quotation marks) into cell B15 then copy =DATEDIF (A12,A13,B15) into cell A15. Same result... your age in days. Next, copy the formula =DATEDIF (A12,A13,"m") into cell A16 and = DATEDIF (A12,A13,"y") into cell A17. The results are your age in completed months and completed years. Return_type "yd" gives the number of days ignoring the years, "ym" gives the number of months, ignoring the years and "md" gives the number of days, ignoring the months and years. Try editing the formula in cell A16 or A17 to see how these work. Now, let's include some text and concatenation (using the ampersand) to see what we can come up with. Copy the following formula (from the = sign to the " after the word old) into cell A18... ="Today I am "&DATEDIF (A12,A13,"y")&" years, "&DATEDIF(A12,A13,"ym") &" months and "& DATEDIF (A12,A13,"md") &" days old" There are several other date functions, some of which are only available from Excel 2007 onwards. DAYS360 (start_date,end_date,method) returns the number of days between two dates based on a 360-day year (twelve 30-day months). EDATE start_date,months) returns the serial number of the date that is the specified number of months before or after start_date. EOMONTH (start_date,months) returns the serial number of the last day of the month before or after a specified number of months. NETWORKDAYS (start_date,end_date,holidays) returns the number of whole work days between two dates, excluding specified holidays. This function considers Monday to Friday as work days and holidays can be specified by dates or serial values. WEEKDAY (serial_number,return_type) returns a number between 0 and 6 or between 1 and 7 (depending on the return_type) that identifies the day of the week. It's easier to use custom format "ddd" or "dddd". WEEKNUM (serial-num,return_type) converts a serial number (date) to a number representing the week number (numerically) in a year. There are a few quirks to this function -- type WEEKNUM into the search field of Excel Help for details. WORKDAY (start_date,days,holidays) returns the serial number of the date before or after a specified number of work days, excluding holidays. YEARFRAC (start_date,end_date,basis) returns the fraction of a year that is the number of whole days between start_date and end_date. Now, here are a few interesting things to try on your worksheet. Add days to a date... Enter a date into cell A19. Any date will do -- and format it however you like. Then in cell A20 enter the formula =A19+1 to add one day to the date in cell A19. Next, copy the formula and paste it into cells A21 to A25. Convert to values... Select cells A20 to A25 and convert the formulas to values (copy, right click over one of the cells, select Paste Special on the pop-up menu, then select Values and click on OK. Then press Esc to turn off the marching black ants). Fill dates down... Select the bottom couple of cells and drag the fill handle in cell A25 down about 40 or 50 rows. (The fill handle is the small black square at the bottom right of the selected cells). When the mouse button is released, the Auto Fill Options button will appear next to the bottom cell. Click on that button and select Fill Weekdays on the pop-up menu. Now look carefully at the dates. See what day each date is... Scroll back up to row 20 and enter the formula =A20 into cell B20. Then custom format cell B20 as "ddd". To do that, right click over B20, select Format Cells, select Custom on the Number tab of the Format Cells dialogue box, then overtype whatever is in the Type field with ddd (without quotation marks). Fill the days down... With cell B20 selected, double click on the fill handle to put the day into each cell next to the dates. Excel has copied the formula from cell B20 down, adjusting the cell reference in each cell and has also copied the formatting. Show Year and Month in one cell... In a database containing a column of dates it is often desirable to filter or summarise the data by months, and an easy way to do that is to add a column that shows the year and month (as a decimal number) alongside each date. Here's how. Select cell C20 and format it as a number with two decimal places (do not show the comma separator). Then copy the formula =YEAR(A20)+(MONTH(A20)/100) into cell C20 and double click on the fill handle to copy the formula (and formatting) down. Then insert three blank rows above row 20 (select rows 20 to 22, right click, Insert) and enter some numbers into column D from cell D23 to the bottom of the database (enter 1, 2, 3 into cells D23, D24 and D25 then double click on the fill handle). Put the column headings Date, Day, Y.m and No into cells A22 to D22, make them bold and centred in their cells. Next, turn auto filter on (select a cell in the database, then in Excel 1997-2003 click on Data, Filter, Auto Filter or in Excel 2007 or later click on the Data tab then the Filter icon). Now copy this formula =SUBTOTAL(9,D23:D100) into cell D21 and filter the database for one of the months in the Y.m column. Take note of the subtotal, then filter Y.m for a different month and look at the subtotal. So there you have it... Dates in Excel are numbers that can be sorted, formatted or calculated in various ways using functions or ordinary formulas -- and they can be used to filter a database. The only drawback is that dates prior to January 1, 1900 (in the 1900 date system) or prior to January 2, 1904 (in the 1904 date system) can't be entered into Excel as dates. They have to be entered as text and can't be used in numeric formulas or date functions... unless the old dates are converted to dates that Excel will recognize as dates. First, a little explanation... Leap years are evenly divisible by four, unless they are century years, e.g. 1900, in which case they must be evenly divisibly by 400. That means the calendar (including which days fall on which dates) repeats itself every 400 years. So... Press Ctrl+Home on your test worksheet and enter May 9, 1611 as 09/05/1611 (Australian date format) or 05/09/1611 (US date format) into cell C1. Although that cell entry looks like a date to you, to Excel it is text. Next, if dates in your computer are set to dd/mm/yyyy format, copy the first formula below into cell D1, but if you use the mm/dd/yyyy date format, copy the second formula below into cell D1... =DATE (VALUE(RIGHT(C1,4))+400,VALUE(MID(C1,4,2)),VALUE(LEFT(C1,2)))=DATE (VALUE(RIGHT(C1,4))+400,VALUE(LEFT(C1,2)),VALUE(MID(C1,4,2))) That will give you a date that is 400 years after the 1611 date -- and that is a valid Excel date that can be used in calculations etc. Alternatively, if you just want to see what day of the week May 9, 1611 was, and if you use Excel 2007 or later, download the free Excel file named Perpetual Diary and Calendar.xlsx from the near the bottom of the Free Downloads page of the web site (link below). It contains a calendar that goes from year 1066 to year 3000. By James F Pearson -- You wondered how you'd make it through. I wondered what was wrong with you. Because how could you give your love to someone else, yet share your dreams with me? Sometimes the only thing you're looking for, is the one thing you can't see. Vanessa Williams Notice: This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this email by error, please notify us by return e-mail or telephone and immediately and permanently delete the message and any attachments. The recipient should check this email and any attachments for the presence of viruses. The Reserve Bank of India accepts no liability for any damage caused by any virus transmitted by this email. Search for old postings at: http://www.mail-archive.com/[email protected]/ To unsubscribe send a message to [email protected] with the subject unsubscribe. To change your subscription to digest mode or make any other changes, please visit the list home page at http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
