Article ID: 303216  View products that this article applies to. This article was previously published under Q303216
For a Microsoft Excel 2000 version of this article, see 214094
(http://support.microsoft.com/kb/214094/
)
.
On This PageSUMMARY Microsoft 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/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 builtin date and time functions. MORE INFORMATIONThe TODAY() and NOW() FunctionsThe 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 NumberIn Microsoft Excel, dates are sorted based on the serial number of the date, instead of on the displayed number. Therefore, when you sort dates in Microsoft 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 NumberBecause serial numbers are also used in date and time comparisons, actual results may be different from 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) =IF(TODAY()=DATEVALUE("10/1/92"),TRUE,FALSE) How to Work with Date FormulasHow to Find the Number of Days Between Today and a Future DateTo find the number of days between now and a date sometime in the future, use the following formula
="mm/dd/yy"TODAY()
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 DatesTo 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:
4 years, 1 months, 6 days 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 =A2DATE(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" How to Find the Number of Weekdays Between Two DatesTo 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:
Note If you see #NAME as the result, click AddIns on the Tools menu. Click to select the Analysis ToolPak check box. If the Analysis ToolPak check box is not listed in the AddIns dialog box, you must install it from the Office Value Pack. For additional information about the Value Pack, click the following article number to view the article in the Microsoft Knowledge Base: 276444
(http://support.microsoft.com/kb/276444/
)
What's installed with the Office 2001 value pack
How to Increase Dates IncrementallyTo increase a date by a number of years, months, or days, use the formula
=DATE(YEAR(reference)+value1,MONTH(reference)+value2,DAY(reference)+value3)
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.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 FormulasHow to Calculate Elapsed TimeWhen 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 =B2A2, 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 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:45
=B2A2+IF(A2>B2,1)
How to Accumulate Hours and Minutes Greater Than 24 HoursIf you want to display a time greater than 24 hours correctly, you can use the 37:30:55 builtin 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 EquivalentTo 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 24hour 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:
=(TimeINT(Time))*24
For example, if cell A1 reads "6/20/96 4:30 AM", the formula is:
=(A1INT(A1))*24
The result again is 4.5. How to Convert a Decimal Number to Its Date EquivalentTo 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 24hour 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 Excel for the Macintosh and Excel for WindowsBy default, Excel for the Macintosh 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 the Macintosh 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 the Macintosh 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.In Microsoft Excel for Mac, you can change to the 1900 date system by clicking Preferences (on the Edit menu in Excel 2001 for Mac, or on the Excel menu in later versions), clicking the Calculation tab, and then clicking to clear the 1904 date system check box. In Excel for Windows, you can change to the 1904 date system by clicking Options on the Tools menu, clicking the Calculation tab, and then clicking to select the 1904 date system check box. 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. REFERENCES
For additional information about calculating dates in
Excel, click the following article numbers to view the articles in the Microsoft Knowledge Base:
214068
(http://support.microsoft.com/kb/214068/
)
Date values earlier than 1900
appear as text
214330
(http://support.microsoft.com/kb/214330/
)
Description of the differences between the 1900 date system and the 1904 date system in Excel
214233
(http://support.microsoft.com/kb/214233/
)
Text or
number converted to unintended number format
179327
(http://support.microsoft.com/kb/179327/
)
Maximum
times allowed in Microsoft Excel for the Macintosh
241072
(http://support.microsoft.com/kb/241072/
)
Dates
and times displayed as serial numbers when viewing formulas
298368
(http://support.microsoft.com/kb/298368/
)
How to control and understand settings in the Excel for Mac Format Cells dialog box
PropertiesArticle ID: 303216  Last Review: October 6, 2011  Revision: 4.0 APPLIES TO
