Dates are often an important part of data analysis. Often you ask questions such as when the product was purchased, how long will the task take on the project, or what is the average revenue for the fiscal quarter? Entering the correct dates is imperative to ensure accurate results. However, to properly understand these results, you need to format the dates to make them easier to understand.
Important: Since the rules governing how dates are interpreted by any calculation program are complex, you should be as specific as possible for the dates you enter. This will ensure the date calculations are as accurate as possible.
Understanding Calculations and Date Formats
Excel stores dates as sequential numbers called sequential values. For example, in Excel for Windows, January 1, 1900 is ordinal 1, and January 1, 2008 is ordinal 39448 because it is 39,448 days after January 1, 1900.
Excel stores time values as decimal fractions because time is treated as a fraction of a day. A decimal number is a value in the range 0 (zero) to 0.99999999, representing a time from 0:00:00 (12:00:00 am) to 23:59:59 (11:59:59 pm).
Since date and time values are values, they can be added, subtracted, and included in other calculations. You can view a date as an ordinal value and a time as a decimal fraction by changing the format of a cell containing a date or time to a general format.
Understanding the two date systems
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.
Excel for Windows was originally based on the 1900 date system because it improved compatibility with other spreadsheet programs developed for the MS-DOS operating system and Microsoft Windows and thus became the standard date system. Excel for Mac was originally based on the 1904 date system because it improved compatibility with early Macintosh computers that did not support dates prior to January 2, 1904, so it became the default date system.
The table below shows the First Date and Last Period Date for each date system and the sequential values associated with each date.
Because the two date systems use different starting days, the same date is represented by different serial numbers in each date system. For example, July 5, 2007 can have two different consecutive values depending on the date system used.
The difference between the two date systems is 1,462 days; This means that the serial number of a date in the 1900 date system is always 1,462 days less than the consecutive value of the same date in the 1904 date system. Conversely, the serial number of a date in the 1904 date system is always 1,462 days less than the consecutive value of the same dates in the 1900 date system. 1,462 days equals 4 years and one day (including one leap day).
Changing the way a two-digit year is interpreted
Important: To interpret the year values correctly, enter four digits (for example, 2001, not 01). By entering four-digit years, Excel will not interpret this century.
If you entered a two-digit date of the year in a text cell or a text argument in a function, such as "= year" ("1/1/31"), Excel interprets the year as follows:
00 – 29 interpreted as the years 2000 to 2029. For example, when entering a date 5/28/19 Excel thinks the date might be May 28, 2019.
from 30 to 99 interpreted as the years 1930 to 1999. For example, when entering a date 5/28/98 Excel thinks the date might be May 28, 1998.
In Microsoft Windows, you can change how the two-digit year is interpreted for all installed Windows programs.
Windows 10
Control Panel and then select Control Panel.
In chapter clock, language and region click.
Click the Language icon and regional standards.
In the dialog box region press the button Extra options.
Open the tab date .
In field
Click the button OK.
Windows 8
Search Search), enter in the search box control panel element Control Panel.
In chapter clock, language and region click.
In the dialog box region press the button Extra options.
Open the tab date .
In field after entering two digits of the year, interpret it as the year between frames, changing the upper limit for the eyelid.
As the upper limit changes, the year automatically changes to the lower limit.
Click the button OK.
Windows 7
Click the button Start and select the item Control Panel.
Select an item.
In the dialog box region press the button Extra options.
Open the tab date .
In field after entering two digits of the year, interpret it as the year between frames, changing the upper limit for the eyelid.
As the upper limit changes, the year automatically changes to the lower limit.
Click the button OK.
Change the default date format to display the year in four digits
By default, when you enter dates in a book, dates are formatted to display two digits for the year. When you change the default date format to a different format using this procedure, the display of dates previously entered in the workbook will be changed to the new format until formatted in the dialog box. " Cell format"" (on the home in Group number press the button dialog box call).
Windows 10
In the search box on the taskbar, type Control Panel and then select Control Panel.
In chapter clock, language and region click change date, time and number formats .
Click the Language icon and regional standards.
In the dialog box region press the button Extra options.
Open the tab date .
In a short list Date format
Click the button OK.
Windows 8
Swipe in from the right edge of the screen, press the button Search(or, if you are using a mouse, hover your mouse over the upper-right corner of the screen, move it down, and click Search), enter in the search box control panel element and then press or Select Control Panel.
In chapter clock, language and region click change date, time or number formats.
In the dialog box region press the button Extra options.
Open the tab date .
In a short list Date format select a format that uses four digits for the year (yyyy).
Click the button OK.
Windows 7
Click the button Start and select the item Control Panel.
Select item language and regional standards.
In the dialog box region press the button Extra options.
Open the tab date .
In a short list Date format select a format that uses four digits for the year (yyyy).
Click the button OK.
Changing the date system in Excel
The date system changes automatically when you open a document created on another platform. For example, if you are working in Excel and open a document that was created in Excel for Mac, the " 1904 date system"is installed automatically.
You can change the date system by following the steps below.
Please select File > Parameters > Additionally.
In chapter when calculating this book select the book you want and then check or uncheck the box use the 1904 date system .
Problem: I am having problems with dates between workbooks using different date systems
You may run into problems when copying and pasting dates, or creating xrefs between workbooks based on two different date systems. Dates can be displayed four years earlier or later than the expected date. These problems can occur if 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 it into a workbook that uses the 1904 date system, the date appears as July 5, 2011, which is 1,462 days later. Also, if you copy the date July 5, 2007 from a workbook that uses the 1904 date system, and then paste it into a workbook that uses the 1900 date system, the date will be July 4, 2003, which is 1,462 days earlier. ... For general information, see Date systems in Excel.
Correct copy and paste error
To set the date to four years and one day later, add 1,462 to the date. Example
Sheet1! $ A $ 1 + 1462
To set the date to four years and earlier, subtract 1,462 from it. Example
Sheet1! $ A $ 1-1462
additional information
You can always ask the Excel Tech Community a question, ask the Answers community for help, or suggest a new feature or improvement on the Excel User Voice website.
The easiest and fastest way to enter the current date or time into a cell is to press the hotkey combination CTRL + ";" (current date) and CTRL + SHIFT + ";" (current time).
It is much more efficient to use the TODAY () function. After all, it not only sets, but also automatically updates the cell value every day without user intervention.
How to put the current date in Excel
To insert the current date in Excel, use the TODAY () function. To do this, select the tool "Formulas" - "Date and Time" - "TODAY". This function has no arguments, so you can just type in the cell: "= TODAY ()" and press Enter.
Current date in cell:
If it is necessary for the cell to automatically update the value of not only the current date, but also the time, then it is better to use the "= TDATA ()" function.
The current date and time in the cell.
How to set current date in Excel on headers and footers
Inserting the current date in Excel is implemented in several ways:
- By setting the parameters of the headers and footers. The advantage of this method is that the current date and time are stamped on all pages at once.
- Using the TODAY () function.
- Using the hotkey combination CTRL +; - to set the current date and CTRL + SHIFT +; - to set the current time. Disadvantage - in this method, the cell value will not be automatically updated to the current indicators when opening the document. But in some cases, data disadvantage is an advantage.
- Using VBA macros using the following functions in the program code: Date (); Time (); Now ().
Headers and footers allow you to set the current date and time at the top or bottom of the pages of the document that will be output to the printer. In addition, the header and footer allows us to number all the pages of the document.
To make the current date in Excel and pagination using headers and footers, do this:
Headers and footers allow us to do more than just set dates and pagination. You can also add a place for the signature of the person responsible for the report. For example, let's edit now the bottom left of the page in the header and footer area.
Each transaction is carried out at some time or period, and then tied to a specific date. In Excel, dates are converted integers. That is, each date has its own integer, for example, 01/01/1900 is the number 1, and 02/01/1900 is the number 2, etc. The definition of years, months and days is nothing more than the corresponding type of formatting for successive numeric values. For this reason, even the simplest date operations performed in Excel (for example, sorting) are very problematic.
Sort in Excel by date and month
Using the example of the table shown in the figure below, we will show in what way to prepare it for comfortable sorting of dates. Our list of date transactions covers the range of cells B1: C15.
First of all, we will create a column in which we will number all transactions. Consider this action as good form for any sort of sorting. Indeed, in such cases, if the data is incorrectly reshuffled, we always have the opportunity to return to the original view of the table. For this:
As a result, the column will be automatically filled with a sequence of transaction numbers from 1 to 14.
Useful advice! In Excel, most tasks have several solutions. To automatically normalize columns in Excel, you can use the right mouse button. To do this, you just need to move the cursor over the keyboard cursor marker (in cell A2) and hold only the right mouse button and drag the marker along the column. After releasing the right mouse button, a context menu will automatically appear from which you need to select the "Fill" option. And the column will be automatically filled with a sequence of numbers, similar to the first autocomplete method.
Now it's time to create auxiliary columns that will significantly help sort the list of transactions in a high-quality manner. There are a few simple steps to follow:
- Fill in cells D1, E1, F1 with the names of the headings: "Year", "Month", "Day".
- For each column, enter the appropriate functions under the headings and copy them along each column:
- D1: = YEAR (B2);
- E1: = MONTH (B2);
- F1: = DAY (B2).
As a result, we should get the following result:
Our table is ready and provides the ability to perform multilateral sorting of transactions by date. First, let's do a test sort to see how things work.
Let's say we want to sort the transaction dates by month. In this case, the order of days and years does not matter. To do this, simply go to any cell in the Month column (E) and select the tool: DATA - Sort & Filter - Sort Ascending.
Now, in order to reset the sorting and bring the table data back to its original form, go to any cell in the "No." column (A) and you will again select the same "Sort ascending" tool.
How to sort dates by multiple conditions in Excel
And now you can start complex sorting of dates according to several conditions. The task is the following - transactions must be sorted in the following order:
- Years ascending.
- Months in the period of certain years - descending.
- Days in periods of specific months - descending.
The way to implement the task:
As a result, we performed a complex sorting of dates according to several conditions:
For sorting table values in Date format, Excel provides drop-down list options such as Old to New and New to Old. But in practice, when working with large amounts of data, the result does not always meet expectations. Since for Excel, dates are integers, it is safer and more efficient to sort them using the method described in this article.
Excel spreadsheets provide the ability to work with various types of text and numerical information. Date processing is also available. In this case, there may be a need to isolate a specific number from the total value, for example, a year. There are separate functions for this: YEAR, MONTH, DAY and WEEKDAY.
Examples of using functions for processing dates in Excel
Excel tables store dates that are presented as a sequence of numeric values. It begins on January 1, 1900. This date will correspond to the number 1. At the same time, January 1, 2009 is laid down in the tables as the number 39813. This is the number of days between two designated dates.
The YEAR function is used in a similar way to the adjacent ones:
- MONTH;
- DAY;
They all display numerical values corresponding to the Gregorian calendar. Even if the Hijri calendar was selected in the Excel spreadsheet to display the entered date, then when extracting the year and other composite values using functions, the application will present a number that is the equivalent in the Gregorian chronology system.
To use the YEAR function, enter the following function formula with one argument in a cell:
YEAR (numeric cell address with date)
The function argument is required. It can be replaced with "date_num_format". In the examples below, you can clearly see this. It is important to remember that when displaying the date as text (automatic orientation along the left edge of the cell), the YEAR function will not be performed. This will result in the #VALUE display. Therefore, formatted dates must be numeric. Days, months and years can be separated by periods, slashes, or commas.
Let's consider an example of working with the YEAR function in Excel. If we need to get the year from the original date, the RIGHT function will not help us, since it does not work with dates, but only text and numeric values. To separate the year, month or day from the full date, Excel provides functions for working with dates.
Example: There is a table with a list of dates and in each of them it is necessary to separate the value of only the year.
Let's enter the initial data into Excel.
To solve the problem, you need to enter the formula in the cells of column B:
YEAR (the address of the cell, from the date of which you want to extract the value of the year)
As a result, we are extracting the years from each date.
A similar example of the MONTH function in Excel:
An example of working with the DAY and WEEKDAY functions. The DAY function gets to calculate the number of any day from a date:
The WEEKDAY function returns the number of the day of the week (1st Monday, 2nd Tuesday ... etc.) for any date:
In the second optional argument to the WEEKDAY function, specify the number 2 for our day of the week format (from Monday-1 to Sunday-7):
If you omit the second optional argument, then the default format (English Sunday-1 through Saturday-7) will be used.
Let's create a formula from combinations of the INDEX and WEEKDAY functions:
Let's get a clearer view of the implementation of this function.
Examples of practical application of functions for working with dates
These primitive functions are very useful for grouping data by year, month, day of the week, and specific day.
Let's say we have a simple sales report:
We need to quickly organize data for visual analysis without using pivot tables. To do this, we will present the report in a table where you can conveniently and quickly group data by years, months and days of the week:
We now have a tool for working with this sales report. We can filter and segment data according to specific time criteria:
In addition, you can draw up a histogram to analyze the best-selling days of the week, understand which day of the week has the most sales:
In this view, it is very convenient to segment sales reports for long, medium and short periods of time.
It should be noted right away that in order to get the difference between two dates, none of the above functions will help us. For this task, you should use the dedicated RAZDAT function:
The type of values in the "date" cells requires a special approach when processing data. Therefore, you should use the appropriate functions for this type in Excel.
Let's create a sequence of dates and times of various kinds:01.01.09, 01.02.09, 01.03.09, ..., Jan, Apr, Jul, ..., Mon, Tue, Wed, ..., Q1, Q2, ..., 09: 00, 10:00, 11:00, ... etc.
Because each date value corresponds to a certain number (see article), then the approaches for forming sequences described in the article are applicable for dates. However, there are also some peculiarities.
Sequence 01.01.09, 01.02.09, 01.03.09 (first days of months) can be formed by the formula = EEC DATE (B2, STRING (A1)), in cell B2 there must be a date - the first element of the sequence ( 01.01.09 ).
The same sequence can be formed using the right mouse button. Let in the cell A2 entered value 01.01.09 ... Select the cell A2 ... While holding down the right mouse button, copy the value from A2 into the cells below. After releasing the right mouse button, a context menu will appear, in which you need to select the item Fill in by month.
By changing the format of the cells containing the sequence 01.01.09, 01.02.09, 01.03.09, on the MMM(see article) we get the sequence Jan, Feb, Mar, ...
The same sequence can be entered using the autocomplete list Button Office / Excel Options / Basic / Basic settings for working with Excel / Edit Lists(enter jan, then Fill marker copy down).
The cells will not contain dates, as in the previous case, but text values.
Similarly, you can form a sequence of days of the week. mon, tue, wed, ...
Sequence of quarters 1 sq., 2 sq., ... can be formed using ideas from the article.
Using the tool, only weekday sequences can be created. And also with a step of a month and a year.
Sequence of first months of quarters Jan, Apr, Jul, Oct, Jan, ... can be created by entering in two cells the first two elements of the sequence ( Jan, Apr), then (after selecting them) copy down fill marker... The cells will contain text values. To make cells contain dates, use the formula = DATEEC ($ G $ 16, (LINE (A2) -LINE ($ A $ 1)) * 3) The sequence is assumed to start at cell G16 , the formula must be entered into the cell G17 (see example file).
Time sequence 09:00, 10:00, 11:00, ... can be formed using. Let in the cell A2 entered value 09 :00 ... Select the cell A2 ... Let's copy Fill marker, value from A2 into the cells below. The sequence will be formed.
If you want to generate a time sequence in 15 minute increments ( 09:00, 09:15, 09:30, ... ), then you can use the formula = B15 + 1/24/60 * 15 (It is assumed that the sequence begins with cell B15 , the formula must be entered in B16 ). The formula will return the result in date format.
Another formula = TEXT (B15 + 1/24/60 * 15; "hh: mm") will return the result in text format.
