The Excel Date Function



Here is the guide to the Excel Date function:

Excel stores dates as a serial number giving each day of each year a unique number. The numbering system starts with 'day 1' being the 1st January 1900, 'day 2' being the 2nd January 1900, and so on.

Here is the Excel DATE Function Syntax:
DATE(year,month,day)

Year
The year argument can be one to four digits.

  • If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(100,1,2) returns January 2, 2000 (1900+100).
  • If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2000,1,2) returns January 2, 2000.
  • If year is less than 0 or is 10000 or greater, Excel returns the #NUM! error value.

    Month
    Month is a number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified. Example: DATE(1996,14,2) returns the serial number representing February 2, 1997.

    Day
    Day is a number representing the day of the month. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. Example: DATE(1996,1,35) returns the serial number representing February 4, 1996.

    Mathematical Date Functions

    To calculate the difference between two dates in days or weeks

  • In the cell A1 enter the first date. In the cell A2 enter the second date.
  • In the cell A3 enter the formula =A2-A1 to calculate the difference between the second date and the first date.
  • This formula calculates the difference between the two dates in days.
  • To calculate the difference in weeks use the formula =(A2-A1)/7.

    To add days or weeks to a date

  • Enter a date in cell A1.
  • In cell A2 enter the following formula: =A1+120. This adds 120 days to the date.
  • To add weeks to a date, multiply the number of weeks by seven to calculate the number of days, i.e. =A1+(10*7) adds ten weeks to the date.

    To add months to a date

  • Enter a date in cell A1.
  • In cell A2 enter the following formula: =DATE(Year(A1), MONTH(A1)+1, DAY(A1))
  • If you change the +1 value to +2 or +3, you will add two months or three months on respectively.

    To find out the last day of the month

  • Enter a date in cell A1.
  • In cell A2 enter the following formula: =DATE(Year(A1), MONTH(A1)+1, 1)-1
  • This will give you a date that represents the last day of the month found in cell A1.

    Get FREE ebooks!
    (Click for more info)

    Subscribe to...
    MS Office Tips Newsletter

    E-mail Address:
    First Name:
    Then

    Don't worry -- your e-mail address is totally secure.
    I promise to use it only to send you MS Office Tips.

    Can't find what you're looking for? Try Google Search!

    Google
     

    Back to Top


    >> Next Topic : Creating Excel Pie Chart - The Step-by-step guide.

    << Previous Topic : Using the Excel CHOOSE( ) Function


    You're viewing the Excel DATE Function info page, click here to go back to the Home Page