Microsoft KB Archive/214094

From BetaArchive Wiki

Article ID: 214094

Article Last Modified on 2/1/2007


APPLIES TO

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition

This article was previously published under Q214094

For a Microsoft Excel 2001 for Mac version of this article, see 303216.



For Microsoft Office Excel 2007, visit the Office Online Excel 2007 Help and How-to Web page.

SUMMARY

This step-by-step article describes how to use the built-in date and time functions in Microsoft Excel to perform complex date and time calculations.

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.

The TODAY() Function and the NOW() Function

The TODAY function returns the serial number of today's date based on your system clock and does not include the time. The NOW function returns the serial number of today's date and includes the time.

How Dates Are Sorted Based on Serial Number

In Excel, dates are sorted based on the serial number of the date, instead of on the displayed number. Therefore, when you sort dates in Excel, you may not receive the results you expect.

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.

How Dates Are Compared Based on Serial Number

Because serial numbers are also used in date and time comparisons, actual results may be different than what you expect (based on the displayed values).

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 dates 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)

How to Work with Date Formulas

How to Find the Number of Days Between Today and a Future Date

To find the number of days between now and a date sometime in the future, use the following formula 

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

where "mm/dd/yy" is the future date. Use the General format to format the cell that contains the formula.

How to Find the Number of Days, Months, and Years Between Two Dates

To calculate the number of days, months, and years between two dates, where the start and end dates are entered in cells A1 and A2 respectively, follow these steps:

  1. Create a new workbook.

    [GRAPHIC: Excel workbook]

  2. In the New Workbook pane, click Blank workbook.

    [GRAPHIC: Microsoft Excel Workbook]

  3. Type the following data in the workbook:

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



    [GRAPHIC: Cells A1 and A2]

  4. 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"



    [GRAPHIC: Cell D1]

    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.

If you type the formula correctly, cell D1 displays: 

   4 years, 1 months, 6 days


[GRAPHIC: Cell D1]

Additional Formula Breakdown for Days, Months, and Years

This formula can also be broken down into individual segments of days, months, and years as follows.

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 days between two dates, ignoring years and months =A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&"days"
The remaining number of months between two dates, ignoring years and days =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"
The number of whole years between two dates =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<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.

How to Find the Number of Weekdays Between Two Dates

To find the number of weekdays between two dates, where the start and end dates are typed in cells A1 and A2 respectively, follow these steps:

  1. Create a new workbook.

    [GRAPHIC: Microsoft Excel Workbook]

  2. In the New Workbook pane, click Blank workbook.

    [GRAPHIC: Microsoft Excel Workbook]

  3. Type the following data in the workbook:

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


    [GRAPHIC: Cells A1 and A2]

  4. In cell D1, type the following formula:

       =NETWORKDAYS(A1,A2)


    [GRAPHIC: Cell D1]

    "1071" appears as the result in cell D1.

    [GRAPHIC: Cell D1]

Note If you see #NAME as the result, click Add-Ins on the Tools menu. Click to select the Analysis ToolPak check box, and then click Yes if you are prompted as to whether you want to install it.

How to Increase Dates Incrementally

To increase a date by a number of years, months, or days, use the following formula, where reference is either the date value or cell reference that contains the date, and value1, value2, and value3 are the increments by which you want to increase the year, month, and day, respectively. 

   =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")))

How to Work with Time Formulas

How to Calculate Elapsed Time

When you subtract the contents of one cell from another to find the amount of time elapsed between them, the result is a serial number that represents the elapsed hours, minutes, and seconds. To make this number easier to read, use the h:mm time format in the cell that contains the result.

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


[GRAPHIC: Cell D2]

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)

How to Accumulate Hours and Minutes Greater Than 24 Hours

If you want to correctly display a time greater than 24 hours, you can use the 37:30:55 built-in format. If you want to use a custom format instead, you must enclose the hours parameter of the format in brackets, for example: 

   [h]:mm

How to Convert a Date to Its Decimal Number Equivalent

To convert a serialized date (h:mm:ss) to a decimal number (0.00), you must convert the serial number to a decimal by converting to a 24-hour base. You do this by multiplying the time by 24 as follows 

   =Time*24

where Time is the number that you want to convert from a time format to a decimal number; this number can be a cell reference or a string of numbers in the TIMEVALUE function.

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

   =A1*24

The result is 4.5.

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

   =(Time-INT(Time))*24

For example, if cell A1 reads "6/20/96 4:30 AM", the formula is: 

   =(A1-INT(A1))*24

The result again is 4.5.

How to Convert a Decimal Number to Its Date Equivalent

To convert a decimal number (0.00) to its serial date equivalent (h:mm:ss), you must convert the serial number to a decimal by converting to a 24-hour base. You do this by dividing the time by 24 as follows 

   =Time/24

where Time is the number that you want to convert from a decimal number to a date serial number and can be a cell reference or a real number. For example, if you have a value of 4.5 to represent four hours and 30 minutes in cell A1, the formula is: 

   =A1/24

The result is 4:30.

How to Transfer Files Between Microsoft Excel for Mac and Excel for Windows

By default, Excel for Mac uses the 1904 date system and Excel for Windows uses the 1900 date system. This means that when you type the serial number 1 in Excel for Mac and format it as a date, Excel displays it as 1/2/1904 12:00 a.m. Excel for Windows displays the serial number 1 as 1/1/1900 12:00 a.m.

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:

  1. On the Tools menu, click Options.


[GRAPHIC: Microsoft Excel for Windows]

  1. Click the Calculation tab, and then click to select the 1904 date system check box.


[GRAPHIC: Options dialog box]


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

  1. On the Excel menu, click Preferences.


[GRAPHIC: Microsoft Excel for Mac]

  1. Click the Calculation tab, and then click to clear the 1904 date system check box.


[GRAPHIC: Preferences dialog box]


REFERENCES

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

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



Additional query words: XL2000 reviewdocID XL2007

Keywords: kbnomt kbgraphxlink kbscreenshot kbdatetime kbautomation kbprogramming kbfunctions kbhowtomaster kbmigrate KB214094



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/214094&oldid=369321’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki