You are currently offline, waiting for your internet to reconnect

This article was previously published under Q303216

For a Microsoft Excel 2000 version of this article, see 214094.

In this system, the serial number 1 represents 1/2/1904 12:00:00 A.M. Times that 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/8/1992 1:26:24 A.M.

To help you perform complex date and time calculations, Microsoft 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 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)

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

=`"mm/dd/yy"`-TODAY()

where - Create a new workbook
- Type the following data in the workbook:
A1: 03/25/94 A2: 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 there are no line breaks, or the formula does not work.

4 years, 1 months, 6 daysAdditional 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 =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 above formulas, &" days", &" months", and &" years" are optional. These allow you to distinguish the results as days, months, and years.

- Create a new workbook.
- Type the following data in the workbook:
A1: 03/25/94 A2: 05/01/98

- In cell D1, type the following formula:
=NETWORKDAYS(A1,A2)

Note If you see #NAME as the result, click

276444 What's installed with the Office 2001 value pack

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

where 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:45If 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 instance, you could 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.61458333 D2: 14:45To set up this table, type the following formula in cells C2 and D2:

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

[h]:mm

=`Time`*24

where 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. =`Time`/24

where =A1/24

The result is 4:30. In Microsoft Excel for Mac, you can change to the 1900 date system by clicking

Note You cannot use the preceding calculations on dates before 1/1/1900 12:00 a.m. This is because the way the date is stored, it does not have a negative component; therefore, -1 is not the equivalent of 12/31/1899 11:59 p.m. Dates before the 1900 date always appear as text and cannot be manipulated.

214068 Date values earlier than 1900 appear as text

214330 Description of the differences between the 1900 date system and the 1904 date system in Excel

214233 Text or number converted to unintended number format

179327 Maximum times allowed in Microsoft Excel for the Macintosh

241072 Dates and times displayed as serial numbers when viewing formulas

298368 How to control and understand settings in the Excel for Mac Format Cells dialog box

XL2001KB MAC XL2001 time date xl excel

Properties

Article ID: 303216 - Last Review: 10/06/2011 04:50:00 - Revision: 4.0

- Microsoft Entourage 2008 for Mac
- Microsoft Excel 2008 for Mac
- Microsoft Excel 2004 for Mac
- Microsoft Excel 2001 for Mac
- Microsoft Excel X for Mac
- Microsoft PowerPoint 2008 for Mac
- Microsoft Word 2008 for Mac

- dftsdahomeportal kbhowto KB303216

data-bi-slot="2"> Privacy & cookies