Deconstructing Dates in Excel 2007 with DAY, WEEKDAY, MONTH, and YEAR

The DAY, WEEKDAY, MONTH, and YEAR date functions in Excel 2007 all return parts of the date serial number that you specify as their argument. You can access these functions on the Date & Time command button’s drop-down menu. These functions use the following syntax:

• DAY(serial_number) returnd the day of the month in the date as a number between 1 and 31.

  
  



• WEEKDAY(serial_number,[return_type]) returns the day of the week as a number between 1 and 7 or 0 and 6. The optional return_type argument is a number between 1 and 3; 1 (or no return_type argument) specifies the first type, in which 1 equals Sunday and 7 equals Saturday; 2 specifies the second type, where 1 equals Monday and 7 equals Sunday; and 3 specifies the third type, in which 0 equals Monday and 6 equals Sunday.

  
  



• MONTH(serial_number) returns the number of the month in the date serial number (from 1 to 12).

  
  



• YEAR(serial_number) returns the number of the year (as an integer between 1900 and 9999) in the date serial number.

  
  

For example, if you enter the following DAY function in a cell as follows:

=DAY(DATE(08,4,15))

Excel returns the value 15 to that cell. If, instead, you use the WEEKDAY function as follows:

=WEEKDAY(DATE(08,4,15))

Excel returns the value 4, which represents Wednesday (using the first return_type where Sunday is 1 and Saturday is 7) because the optional return_type argument isn’t specified. If you use the MONTH function on this date as in the following:

=MONTH(DATE(08,4,15))

Excel returns 4 to the cell.

If you've checked your calendar and notice something funny here, perhaps the YEAR function will clear it up. If you use the YEAR function on this date, as in the following:

=YEAR(DATE(08,4,15))

Excel returns 1908 to the cell (instead of 2008).

This means that if you want to enter a year in the 21st century as the year argument of the DATE function, you need to enter all four digits of the date, as in the following:

=DATE(2008,4,15)

Note that you can use the YEAR function to calculate the difference in years between two dates. For example, if cell B12 contains 7/23/1978 and cell C12 contains 7/23/2008, you can enter the following formula using the YEAR function to determine the difference in years:

=YEAR(C12)-YEAR(B12)

Excel then returns 2/9/1900 to the cell containing this formula, which becomes 40 as soon as you apply the General number format to it (by pressing Ctrl+Shift+` or Ctrl+~).

Don’t use these functions on dates entered as text entries. Always use the DATEVALUE function to convert these text dates and then use the DAY, WEEKDAY, MONTH, or YEAR functions on the serial numbers returned by the DATEVALUE function to ensure accurate results.

dummies

Source:http://www.dummies.com/how-to/content/deconstructing-dates-in-excel-2007-with-day-weekda.html