Date values in Excel are numbers
In Excel, a date is the number of days since January 1, 1900 starting with January 1, 1900 being “1”. Each date after that, Excel adds one more number to that sequence. So August 26, 2013 is 41512, or 41,512 days since January 1, 1900.
The integer part of the number is used for the days. The decimal part of the number is the fractional part of the day — or the time. So .5 would be 50% of the way thru the day, or 12:00 noon. That makes 41,512.5 to be equivalent to 12:00 noon on August 26, 2013.
The date number pattern is a follows.
DDDDD.TTTTTT
So as you might imagine, adding 3 to the date value of August 30, 2013 results in September 2, 2013. Internally you’re just adding 3 to 41,512 and getting 41,515.
Built-in functions for date values in Excel
Excel has a number of built in functions that return specific parts of the date (or time) in question or perform specific operations.
Parts of a Date or Time
YEAR()
The YEAR function returns the year from the date value.
YEAR(41512)
Returns 2013.
MONTH()
The MONTH function returns the month from the date value.
MONTH(41512)
Returns 8 (8 for August, the 8th month).
DAY()
The DAY function returns the day of the month.
DAY(41512)
Returns 30 for the 30th day of the month.
HOUR()
The HOUR function returns the hour of the day.
HOUR(41512.5)
Returns 12.
MINUTE()
The MINUTE function returns the minute of the hour.
MINUTE(41512.5)
Returns 0, meaning this time is exactly on the hour (12:00).
SECOND()
The SECOND function returns the second of the hour.
SECOND(41512.5)
Returns 0 as well in this case. The .5 is equivalent to 12:00:00.
Special Date and Time Functions
TODAY()
The TODAY function returns today’s date. This will return just the date part (integer part) of the number.
NOW()
The NOW function returns the date and time right now. NOW will return the full DDDDD.TTTTTT number value.
DATE()
The DATE function will return the date (integer) part of a date value. The time, or decimal part, will be ignored.
TIME()
The TIME function will return the time (decimal) part of a date value. The date, or integer part, will be ignored.
Elapsed Time
DAYS360()
The DAYS360 function returns the number of days between two dates. To find the number of months between two dates, you can read How to Find the Number of Months Between Two Dates in Excel.
NETWORKDAYS()
The NETWORKDAYS function returns the number of work days between two dates.
There’s more…
Excel has other date functions as well. To read a full listing of date functions in Excel you can view this page.
There you go.
