Each line is a separate transaction. You need to make a report for the month. How many products were sold in each month. There are different ways to solve this problem, but let’s assume that our task is to extract the month from the date, so that later it will 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 - to get months you must use capital letter"M". Moreover, the display format will depend on its quantity (First letter of the month, Full name of the month, short name, as a double digit and a single digit)
You can clearly see this in the screenshot.
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, the context menu, 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 Button Office/Excel Options/Basic/Basic Excel Options/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.
Excel tables 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, for example, a year, from the total value. There are separate functions for this: YEAR, MONTH, DAY and WEEKDAY.
Examples of using functions to process dates in Excel
Excel tables store dates that are represented as a sequence numerical values. It begins on January 1, 1900. This date will correspond to the number 1. Moreover, January 1, 2009 is included in the tables as the number 39813. This is exactly the number of days between the two designated dates.
The YEAR function is used similarly to the related ones:
- MONTH;
- DAY;
They all display numerical values according to the Gregorian calendar. Even if in Excel spreadsheet To display the entered date, the Hijri calendar was selected, then when isolating the year and other component values using functions, the application will present a number that is equivalent in the Gregorian chronology system.
To use the YEAR function, you need to enter the following function formula with one argument in a cell:
YEAR (cell address with date in numeric format)
The function argument is required. It can be replaced with "date_in_numeric_format". In the examples below, you can clearly see this. It is important to remember that when displaying the date as text (automatic orientation to the left edge of the cell), the YEAR function will not be executed. Its result will be the display #VALUE. Therefore, formatted dates must be presented in numeric form. Days, months and years can be separated by a period, slash or comma.
Let's look at an example of working with the YEAR function in Excel. If we need to get the year from the source date, the RIGHT function will not help us since it does not work with dates, but only with 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 source data into Excel.
To solve the problem, you need to enter the formula in the cells of column B:
YEAR (address of the cell from the date of which you need to extract the year value)
As a result, we extract 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 day of any day from a date:
The WEEKDAY function returns the number of the day of the week (1-Monday, 2-Tuesday... etc.) for any date:
In the second optional argument of the WEEKDAY function, you should specify the number 2 for our format for counting the day of the week (from Monday-1 to Sunday-7):
If you omit the second optional argument, then the default format (English Sunday-1 to Saturday-7) will be used.
Let's create a formula from combinations of the INDEX and WEEKDAY functions:
Let's get a more understandable form of implementation of this function.
Examples of practical application of functions for working with dates
These primitive functions are very useful in grouping data by: years, months, days of the week and specific days.
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 present the report in a table where you can conveniently and quickly group data by year, month and day of the week:
Now we have a tool to work with this sales report. We can filter and segment data based on certain time criteria:
In addition, you can create a histogram to analyze the best-selling days of the week, to understand which day of the week accounts for the largest number of sales:
In this form, it is very convenient to segment sales reports for long, medium and short periods of time.
It’s worth noting right away that in order to get the difference between two dates, none of the functions described above will help us. For this task, you should use the specially designed RAZNDAT function:
The type of values in date cells requires a special approach when processing data. Therefore you should use appropriate this type functions in Excel.
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. To do 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 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, 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 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:
- Fill in cells D1, E1, F1 with the heading names: “Year”, “Month”, “Day”.
- 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:
- Years in ascending order.
- Months during certain years are in descending order.
- 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 the program Excel dates– these are integers; it is safer and more efficient to sort them using the method described in this article.
