Time and Date Functions in Excel

External

• http://excelsemipro.com/2010/08/date-and-time-calculation-in-excel/
• http://www.cpearson.com/excel/datearith.htm

Internal

• Excel

Overview

Excel maintains Data and Time values internally as number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: dddd.tttttt. This is called a serial date, or a serial date-time.

Dates

The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0. For example, the date 19-Jan-2000 is stored as 36,544 since 36,544 days have passed since 1900-Jan-0. The number 1 represents 1900-Jan-1.

Time

The fractional portion of the number, ttttt, represent the factional portion of a 24 hour day. For example, 6:00 AM is stored as 0.25, or 25% of a 24 hour day. Similarly 6PM is stored as 0.75, or 75% of a 24 hour day. Any date and time can be stored as the sum of the date and the time. For example, 3 PM on 19-Jan-2000 is sore internally as 36544.625. When you enter a time without a value, such as entering 15:00 into a cell, the date portion is zero. Zero indicates that there is no date associated with the time. You should remember that entering just a time does not automatically put in the current date.

Date and Time Arithmetic

Add a Time Interval to a Date

To add 1 hour, 35 minutes and 10 seconds to a time in A1, use the function:

=A1 + TIME(hours, minutes, seconds)

Example:

=A1 + TIME(1, 35, 10)