Dates are often a critical part of data analysis. You often ask questions such as: when was a product purchased, how long will a task in a project take, or what is the average revenue for a fiscal quarter? Entering dates correctly is essential to ensuring accurate results. But formatting dates so that they are easy to understand is equally important to ensuring correct interpretation of those results. Show Important: Because the rules that govern the way that any calculation program interprets dates are complex, you should be as specific as possible about dates whenever you enter them. This will produce the highest level of accuracy in your date calculations. Excel stores dates as sequential numbers that are called serial values. For example, in Excel for Windows, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.). Because dates and times are values, they can be added, subtracted, and included in other calculations. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format. Both Excel for Mac and Excel for Windows support the 1900 and 1904 date systems. The default date system for Excel for Windows is 1900; and the default date system for Excel for Mac is 1904. Originally, Excel for Windows was based on the 1900 date system, because it enabled better compatibility with other spreadsheet programs that were designed to run under MS-DOS and Microsoft Windows, and therefore it became the default date system. Originally, Excel for Mac was based on the 1904 date system, because it enabled better compatibility with early Macintosh computers that did not support dates before January 2, 1904, and therefore it became the default date system. The following table shows the first date and the last date for each date system and the serial value associated with each date.
Because the two date systems use different starting days, the same date is represented by different serial values in each date system. For example, July 5, 2007 can have two different serial values, depending on the date system that is used.
The difference between the two date systems is 1,462 days; that is, the serial value of a date in the 1900 date system is always 1,462 days greater than the serial value of the same date in the 1904 date system. Conversely, the serial value of a date in the 1904 date system is always 1,462 days less than the serial value of the same date in the 1900 date system. 1,462 days is equal to four years and one day (which includes one leap day). Important: To ensure that year values are interpreted as you intended, type year values as four digits (for example, 2001, not 01). By entering four-digit years, Excel won't interpret the century for you. If you enter a date with a two-digit year in a text formatted cell or as a text argument in a function, such as =YEAR("1/1/31"), Excel interprets the year as follows:
In Microsoft Windows, you can change the way two-digit years are interpreted for all Windows programs that you have installed. Windows 10
Windows 8
Windows 7
By default, as you enter dates in a workbook, the dates are formatted to display two-digit years. When you change the default date format to a different format by using this procedure, the display of dates that were previously entered in your workbook will change to the new format as long as the dates haven't been formatted by using the Format Cells dialog box (On the Home tab, in the Number group, click the Dialog Box Launcher). Windows 10
Windows 8
Windows 7
The date system changes automatically when you open a document from another platform. For example, if you are working in Excel and you open a document that was created in Excel for Mac, the 1904 date system check box is selected automatically. You can change the date system by doing the following:
You can encounter problems when you copy and paste dates or when you create external references between workbooks based on the two different date systems. Dates can appear four years and one day earlier or later than the date that you expect. You can encounter these problems whether you are using Excel for Windows, Excel for Mac, or both. For example, if you copy the date July 5, 2007 from a workbook that uses the 1900 date system and then paste the date into a workbook that uses the 1904 date system, the date appears as July 6, 2011, which is 1,462 days later. Alternatively, if you copy the date July 5, 2007 from a workbook that uses the 1904 date system and then paste the date into a workbook that uses the 1900 date system, the date appears as July 4, 2003, which is 1,462 days earlier. For background information, see Date systems in Excel. Correct a copy and paste problem
Correct an external reference problem If you are using an external reference to a date in another workbook with a different date system, you can modify the external reference by doing one of the following:
=[Book2]Sheet1!$A$1+1462
=[Book1]Sheet1!$A$1-1462 Need more help?You can always ask an expert in the Excel Tech Community or get support in the Answers community. See AlsoFormat a date the way you want Display numbers as dates or times Enter data manually in worksheet cells Examples of commonly used formulas When moving data from one cell to another in order to maintain the formula of the original cell you must?The one thing to watch for is that the cell references used in the formula are still what you want after you move. Select the cell that contains the formula you want to move. Click Home > Cut (or press Ctrl + X). Select the cell you want the formula to be in, and then click Paste (or press Ctrl + V).
What happens if you double click the right side of a column's header?What happens if you double-click the right side of a column's header? The column width adjusts to fit the largest entry in that column.
Which option would he use for allowing others to open and edit his workbook?Click Share Workbook in the Changes group. On the Editing tab, click to select the Allow changes by more than one user at the same time. This also allows workbook merging check box, and then click OK. In the Save As dialog box, save the shared workbook on a network location where other users can gain access to it.
What is a cell in Excel quizlet?Terms in this set (13)
cell. A cell is the intersection of a row and a column. A cell can contain a label, a numeric value, or a formula. row. Rows are the horizontal divisions of a worksheet that are identified by numbers.
|