Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.

To help you perform complex date and time calculations, Excel includes many built-in date and time functions.

For example, if you sort a series of dates that are displayed in the mmmm date format (so that only the month is displayed), the months are not sorted alphabetically. Instead, the dates are sorted based on their underlying date serial number.

For example, when you use the NOW function to compare a date with the current date, as in the formula

=IF(NOW()=DATEVALUE("10/1/92"),TRUE,FALSE)the formula returns FALSE, even if the current date is 10/1/92; it returns TRUE only when the date is 10/1/92 12:00:00 a.m. If you are comparing two

ates in a formula, and you do not have to have the time included in the result, you can work around this behavior by using the TODAY function instead:

=IF(TODAY()=DATEVALUE("10/1/92"),TRUE,FALSE)

="mm/dd/yy"-NOW()where

1. Create a New workbook.

2. Click Blank Workbook

3. Type the following data in the workbook:

4. If you type the formula correctly, cell D1 displays:

2. Click Blank Workbook

3. Type the following data in the workbook:

A1:03/25/94A2: 05/01/98

Type the following formula in cell D1:

=YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1), DAY(A2)<DAY(A1))),1,0)&" years, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2)<=MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),DAY(A2)>=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" months,"&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" days"

Note: If you copy and paste this formula, make sure that there are no line breaks. If there are line breaks, the formula will not work.

4. If you type the formula correctly, cell D1 displays:

4 years, 1 months, 6 days

Note If you copy and paste these formulas, make sure that there are no line breaks or the formulas will not work.

Time segment Formula ------------------------------------------------------------------------ The remaining number of =A2-DATE(YEAR(A2),MONTH(A2)- days between two dates, IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&"days" ignoring years and months The remaining number of =MONTH(A2)- MONTH(A1)+IF(AND(MONTH(A2) months between two dates, <=MONTH(A1), DAY(A2)<DAY(A1)),11, ignoring years and days IF(AND(MONTH(A2)<MONTH(A1),DAY(A2)>= DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1), DAY(A2)<DAY(A1)),-1)))&" months" The number of whole years =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)< between two dates MONTH(A1),AND(MONTH(A2)=MONTH(A1), DAY(A2)<DAY(A1))),1,0)&" years"Note In the earlier formulas, &" days", &" months", and &" years" are optional. These allow you to distinguish the results as days, months, and years.

1. Create a New workbook.

2. Click Blank workbook.

3. Type the following data in the workbook:

A1: 03/25/94 A2: 05/01/98

=NETWORKDAYS(A1,A2)"1071" appears as the result in cell D1.

=DATE(YEAR(reference)+value1,MONTH(reference)+value2,DAY(reference)+value3)

For example, to increase a date by one month, the formula is:

=DATE(YEAR(DATEVALUE("6/20/96")),MONTH(DATEVALUE("6/20/96"))+1, DAY(DATEVALUE("6/20/96")))

In the following example, if cells C2 and D2 contain the formula =B2-A2, and cell C2 is formatted in the General format, the cell displays a decimal number (in this case, 0.53125, the serial number representation of 12 hours and 45 minutes).

A1: Start Time B1: End Time C1: Difference D1: Difference (General) (h:mm) A2: 6:30 AM B2: 7:15 PM C2: 0.53125 D2: 12:45

If midnight falls between your start time and end time, you must account for the 24-hour time difference. You can do this by adding the number 1, which represents one 24-hour period. For example, you might set up the following table, which allows for time spans beyond midnight.

A1: Start Time B1: End Time C1: Difference D1: Difference (General) (h:mm) A2: 7:45 PM B2: 10:30 AM C2: 0.614583333 D2: 14:45

To set up this table, type the following formula in cells C2 and D2:

=B2-A2+IF(A2>B2,1)

[h]:mm

=Time*24where

For example, if cell A1 contains a time of "4:30" to represent four hours and 30 minutes, the formula is:

=A1*24The result is 4.5.

If the cell contains both a date and a time, use the following formula:

=(Time-INT(Time))*24For example, if cell A1 reads "6/20/96 4:30 AM", the formula is:

=(A1-INT(A1))*24The result again is 4.5.

=Time/24where

=A1/24The result is 4:30.

If you transfer files from Excel for Mac to Excel for Windows, this difference in the date systems should not cause a problem, because the date system is stored in each file. However, if you copy and paste between files with different date systems that originated on different platforms, dates may be displayed four years and one day away from their correct date.

To change to the 1904 date system in Excel for Windows, follow these steps:

- On the
**Tools**menu in Excel 2003, click**Options**. In Excel 2007 and later go to Excel Options. - In Excel 2003, click the
**Calculation**tab, and then click to select the**1904 date system**check box. In Excel 2007 and later click the Advanced option. Scroll down to When calculating this workbook: and select the Use 1904 date system checkbox.

- On the
**Excel**menu, click**Preferences**. - Under Formulas and Lists choose Calculation.
- Under Workbook options click to clear the 1904 date system check box

214233 Text or number converted to unintended number format in Excel

214386 Maximum times in Microsoft Excel

241072 Dates and times displayed as serial numbers when viewing formulas in Excel

264372 How to control and understand settings in the Format Cells dialog box in Excel

reviewdocID XL2007 XL2010

Properties

Article ID: 214094 - Last Review: 02/13/2015 17:19:00 - Revision: 13.0

- Microsoft Office Excel 2003
- Microsoft Office Excel 2007
- Microsoft Excel 2010

- kbnomt kbgraphxlink kbscreenshot kbdatetime kbautomation kbprogramming kbfunctions kbhowtomaster kbmigrate KB214094