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 01/02/1900 is the number 2, etc. Defining years, months and days is nothing more than the appropriate type of formatting for the next numeric values. For this reason, even the simplest operations with dates performed in Excel (for example, sorting) turn out to be very problematic.

Sort in Excel by date and month

Using the example of the table shown in the figure below, we will show how to prepare it for comfortable sorting of dates. Our list of date transactions covers the range of cells B1:C15.

First of all, let's create a column in which we number all transactions. Consider this action as good form for any type of sorting. Indeed, in such cases, if the data is incorrectly shuffled, we always have the opportunity to return to the original form of the table. For this:

As a result, the column will be automatically filled with a sequence of transaction numbers from 1 to 14.

Helpful advice! In Excel, most problems have multiple solutions. To automatically normalize columns in Excel, you can use the right mouse button. To do this, just move the cursor over the keyboard cursor marker (in cell A2) and hold only right button Use your mouse to draw a marker along the column. After releasing the right mouse button, the context menu 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 autofill method.

Now it’s time to create auxiliary columns that will significantly help sort the list of transactions efficiently. You need to follow a few simple steps:

1. Fill in cells D1, E1, F1 with the heading names: “Year”, “Month”, “Day”.
2. According to each column, enter the corresponding 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 multi-way sorting of transactions by date. Let's do a test sort first to make sure everything works.

Let's say we want to sort 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 and Filter” - “Sort Ascending”.

Now, to reset the sorting and return the table data to its original form, go to any cell of the column “No.” (A) and again select the same “Sort Ascending” tool.

How to sort dates by multiple conditions in Excel

Now you can start complex sorting of dates according to several conditions. The task is as follows: transactions must be sorted in the following order:

1. Years in ascending order.
2. Months during certain years are in descending order.
3. Days during certain months are in descending order.

Method for implementing the task:

As a result, we performed a complex sorting of dates according to several conditions:

To sort 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 volumes of data, the result does not always live up to expectations. Since for Excel programs dates are integers; it is safer and more efficient to sort them using the method described in this article.

The simplest and quick way enter into cell current date or time is to press a combination of hot CTRL keys+";" (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 into Excel, use the TODAY() function. To do this, select the “Formulas” - “Date and Time” - “TODAY” tool. This function has no arguments, so you can simply type into 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 “=TDATE()” function.

The current date and time in the cell.

How to set the current date in Excel on headers and footers

Inserting the current date in Excel is implemented in several ways:

1. By setting header and footer options. The advantage of this method is that the current date and time are stamped on all pages at the same time.
2. Using the TODAY() function.
3. Using the hotkey combination CTRL+; – to set the current date and CTRL+SHIFT+; – to set the current time. The disadvantage is this method The cell value will not be automatically updated to the current values ​​when opening the document. But in some cases, the lack of data is an advantage.
4. Using VBA macros using 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 footer allows us to number all the pages of the document.

To make the current date in Excel and page numbering using headers and footers, do this:

Headers and footers allow us to do more than just set dates and page numbers. You can also add a place for the signature of the person responsible for the report. For example, let's now edit the lower left part of the page in the header and footer area.

Let's create sequences of dates and times of various types:01.01.09, 01.02.09, 01.03.09, ..., Jan, Apr, Jul, ..., Mon, Tue, Wed, ..., 1st quarter, 2nd quarter,..., 09: 00, 10:00, 11:00, ... etc.

Because each date value corresponds to a certain number (see article), then the approaches for generating sequences outlined in the article are also applicable for dates. However, there are also some peculiarities.

Subsequence 01.01.09, 01.02.09, 01.03.09 (first days of months) can be formed by the formula =DATEMON(B2,ROW(A1)), in a cell B2 the date must be the first element of the sequence ( 01.01.09 ).

The same sequence can be formed using the right mouse button. Let into the cell A2 value entered 01.01.09 . Select a cell A2 . Hold down the right mouse button and copy the value from A2 in the cells below. After we release 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 MMM(see article) we get the sequence Jan, Feb, Mar, ...

The same sequence can be entered using the autocomplete list Office/button 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.

In a similar way, 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, you can create sequences of only working days. And also in monthly and year increments.

Sequence of first months of quarters Jan, Apr, Jul, Oct, Jan, ... can be created by entering the first two elements of the sequence into two cells ( Jan, Apr), then (having previously selected them) copy down fill marker. The cells will contain text values. To make cells contain dates, use the formula =DATEMON($G$16,(ROW(A2)-ROW($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 into the cell A2 value entered 09 :00 . Select a cell A2 . Let's copy Fill marker, value from A2 in the cells below. The sequence will be formed.

If you need to create a time sequence in 15 minute increments ( 09:00, 09:15, 09:30, ... ), then you can use the formula =B15+1/24/60*15 (Assuming that the sequence begins with the cell B15 , the formula must be entered into 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.

Dates are often an important part of data analysis. Often you ask questions such as: when was the product purchased, how long will a task in a project take to complete, or what is the average revenue for the fiscal quarter? Entering dates correctly is essential to ensure accurate results. However, to properly understand these results, you must format the dates to make them easier to understand.

Important: Because the rules governing how dates are interpreted by any calculation program are complex, you must be as date specific as possible with each entry. This will ensure that date calculations are as accurate as possible.

About calculations and date formats

Excel stores dates as sequential numbers called sequential values. For example, in Excel for Windows, January 1, 1900 is sequence number 1, and January 1, 2008 is sequence number 39448 because it is 39,448 days after January 1, 1900.

Excel stores time values ​​as decimals because time is treated as a fraction of a day. Decimal number is a value ranging from 0 (zero) to 0.99999999, representing the time from 0:00:00 (12:00:00 am) to 23:59:59 (11:59:59 pm).

Because 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 by changing the format of the cell containing the date or time to a common 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 designed for operating system MS-DOS and Microsoft Windows and so she became standard system date Excel for Mac was originally based on the 1904 date system because it improved compatibility with early Macintosh computers, which did not support dates before 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 meaning 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 sequential meanings depending on the date system used.

The difference between the two date systems is 1,462 days; It means that serial number of a date in the 1900 date system is always 1,462 days less than the serial 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 serial value of the same date 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 ensure that year values ​​are interpreted correctly, enter four digits (for example, 2001 rather than 01). If you enter four-digit years, Excel will not interpret the centuries.

If you enter a two-digit year date in a text cell or text argument in a function, such as "=year" ("1/1/31"), Excel interprets the year as follows:

00 – 29 is interpreted as years from 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 years from 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 the way two digits of the year are interpreted for everyone installed programs Windows.

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 click the button Extra options.

Open the tab date .

In field

Click the button OK.

Windows 8

Search Search), enter in the search field Control Panel element Control Panel.

In chapter clock, language and region click .

In the dialog box region click 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 Control Panel.

Select item.

In the dialog box region click 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 as four digits

By default, when you enter dates in the workbook, dates are formatted to display two digit years. When you change the default date format to another 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 tab home in Group number click the button call the dialog box).

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 click the button Extra options.

Open the tab date .

On the short list Date format

Click the button OK.

Windows 8

Swipe from the right edge of the screen, tap Search(or if you're using a mouse, move your mouse to the top right corner of the screen, move it down, and click Search), enter in the search field 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 click the button Extra options.

Open the tab date .

On the 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 Control Panel.

Select an item language and regional standards.

In the dialog box region click the button Extra options.

Open the tab date .

On the 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 automatically changes when you open a document created on another platform. For example, if you are working in Excel and you open a document created in Excel for Mac, the " date system 1904" is installed automatically.

You can change the date system by following the steps below.

Select File > Options > Additionally.

In chapter when calculating this book select the book you want and then check or uncheck the box use 1904 date system .

Problem: Having problems with dates between books that use different date systems

You may encounter problems copying and pasting dates and creating external links between books based on two different systems date Dates can be output four years earlier or later than the expected date. These problems may 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. Additionally, 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 . General information can be found in the Date systems in Excel section.

Fix copy and paste error

To set the date to four years and one day later, add 1,462 to it. Example

Sheet1!$A$1+1462

To set the date to four years or more early version, subtract 1,462 from it. Example

Sheet1!$A$1-1462

additional information

You can always ask a question to an Excel Tech Community specialist, ask for help in the Answers community, and also suggest new feature or improvement on the Excel User Voice website

Each line is a separate transaction. You need to make a report for the month. How many products were sold in each month. Eat different ways solution to this problem, but suppose that we have the task of extracting the month from the date, so that later it would be easy to summarize the data on them.

Method 1. Get month from date using MONTH function in Excel

We write the formula:

MONTH(A2)

We stretch the formula and get the month from the date in the form of a number. 5 is the month of May, 8 is August and so on.

There are similar functions: YEAR, MONTH, WEEK, DAY, HOUR, MINUTES, SECONDS, which work in a similar way. For example, if in our example we write =YEAR(A2), then the result will be 2013 and so on

Sometimes you need to get the month from a date in text format: "January, February, March..." in this case we will use another function.

Method 2. Get month from date using TEXT function in Excel

The syntax will be as follows

TEXT(value, format)

The value is a reference to a cell with a date
Format – You must use a capital letter “M” to get months. Moreover, the display format will depend on its quantity (First letter of the month, Full name of the month, short name, in the form of a double digit and a single digit)
This can be seen clearly in the screenshot.