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:
Year 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.
The year argument can be one to four digits.
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 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.
Can't find what you're looking for? Try Google Search!
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