How to make the date of the month in Excel. Examples of functions for working with dates: year, month and day in excel. Importing a module with a function into a project

Excel spreadsheets provide the ability to work with various types of textual 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 spreadsheets 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 the two designated dates.

The YEAR function is used in a similar way to the adjacent ones:

• MONTH;
• DAY;

They all display numeric values \u200b\u200bcorresponding to the Gregorian calendar. Even if the Hijri calendar was selected in the Excel spreadsheet to display the entered date, when extracting the year and other composite values \u200b\u200busing 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 (cell address with date in numeric format)

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 to the left of the cell), the YEAR function will not be performed. It 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 you need 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 which you want to extract the year value)

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, you should 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 the 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 year, month and day 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 functions described above will help us. For this task, you should use the specially designed RAZDAT function:

The type of values \u200b\u200bin the "date" cells requires a special approach when processing data. Therefore, you should use the corresponding functions in Excel.

Find out how to write date and month in words in Excel (including in the nominative and genitive cases).

Greetings to all, dear readers of the blog site.

When signing any documents, in addition to our own autograph, we very often enter there the current date as a date, month and year. At the same time, we almost never find there a month written in numerical format. After all, for example, a date written as 11/12/2016 is much more difficult to perceive and read than November 12, 2016.

So in Excel, sometimes it is required to indicate the date in this form, so let's take a closer look at what options we have for implementation.

Recording by setting the cell format

There are quite a lot of date display formats in Excel.

Let's write the date into an arbitrary cell and switch to its format (right-click on the cell and select Format Cell, or simply press the key combination Ctrl + 1).

In the window that opens, we are interested in the tab Number:

Among the date formats, select the month record in words and get:

You can select a record with an asterisk (symbol "*") or without, the difference in display will be to change the type of record when changing the time and date settings of the operating system.

Writing with formulas

Thanks for attention!
If you still have questions on the topic of the article, write in the comments.

Each line is a separate deal. You need to make a monthly report. How many items were sold each month. There are different ways to solve this problem, but suppose that we have the task of pulling the month out of the date, so that later it would be easy to sum the data over them.

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

We prescribe the formula:

MONTH (A2)

We stretch the formula and get the month from the date as 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 \u003d YEAR (A2), then the result will be 2013 and so on

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

Method 2... Get month from date with 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 - capital letter "M" must be used 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 one digit)
You can clearly see this in the screenshot.

The module for writing the date in words, which we will now consider, can find wider application (in particular, it will come in handy when drawing up payment orders and invoices). The task is to force Excel to write the date in words, for example, the date 12.02.2002 is represented as the text "dated February 12, 2002".

This problem can be solved by the following method: first, split the date into components, day, month and year (and for a month, form not its ordinal number in the year, but a name), and then combine all the components in a text line.

A table built on the basis of this algorithm, with formulas and a numerical example, is shown in Fig. 6.7.

Table cells perform the following functions:

• cell A1 - serves as an input (contains the date in Excel format);
• cell A2 - defines the number of the day of the month;
• cell A3 - defines the number of the month;
• cell A4 - defines the year;
• cells А5: А15 - provides spelling of the name of the month;
• cell A16 - adds a zero before the number of the day of the month if this number is less than or equal to 9;
• cell A17 - serves as an exit (contains the date in words).

Figure: 6.7.

So, we need to select the day, month and year in the date (cells A2, A3 and A4). To determine the day, we use the DAY function belonging to the Date and Time category. The function returns the number of the day in the month for the date specified in numeric format. The day is returned as an integer from 1 to 31. The function syntax is as follows:

DAY (date_num_format)

Place the tabular cursor in cell A2 and call the panel of the DAY function (Fig. 6.8). In field Date_as_number enter the address of cell A1. You should receive in cell A2 the formula that determines the day of the month indicated in the date entered in cell A1:

DAY (A1)

To determine the year, we use the YEAR function, which also belongs to the Date and Time category. The function returns the year corresponding to date_in_numeric_format. The year is defined as an integer in the range 1900 to 9999. The syntax for the function is as follows:

YEAR (date_num_format)

Figure: 6.8.

Place the tabular cursor in cell A3 and call the panel of the YEAR function (Fig. 6.9). In field Date_as_number enter the address of cell A1. The formula in cell A3 should look like this:

YEAR (A1)

Thus, this cell will contain the number of the year from the birth of Christ in the date specified in cell A1.

Figure: 6.9.

YEAR Now let's start forming the name of the month. To determine the month number, we will use the MONTH function belonging to the Date and Time category. The function returns the month corresponding to the date_in_num_format argument. The month is defined as an integer from 1 (January) to 12 (December). The function syntax is as follows:

MONTH (date_num_format)

Place the table cursor in cell A4 and call the MONTH function panel (Fig. 6.10). In field Date_as_number enter the address of cell A1. The resulting formula should look like this:

MONTH (A1)

In this case, the MONTH function determines which month number is indicated in the date located in cell A1.

Figure: 6.10.

In area A5: A15 it is necessary to place formulas based on the logical function IF. With their help, an algorithm is implemented that is similar to the algorithm used to determine the name of the day of the week. However, instead of the day of the week, the month is now in the year. In cell A5, the name of the month is formed in the genitive case. Enter the following formula in cell A5:

In cells A6: A14, place similar formulas for other months, and in cell A15, enter a slightly different formula:

We want the day number, if less than 10, to be represented with a leading zero. This can be done using the formula placed in cell A16

IF (A2

If the ordinal of the day of the month is less than or equal to 9, the IF function returns 0. Otherwise, it returns an empty string because nothing was entered between the quotes in the third argument.

The result is formed in cell A17 using the formula

\u003d "from" & A16 & A2 & "" & A5 & "" & AZ & "year."

In this formula, the "&" symbols are used to concatenate (join) text strings and the contents of the cells in which the previously defined date elements are located. The preposition "from" is added before the date (this is necessary, in particular, for a payment order). This is followed by the address of cell A16. It is intended to insert a leading zero before the date in the case when the day number is less than 10. Specifying the address of cell A2 leads to the insertion of the day of the month. This is followed by a space ("") before the month name. Cell A5 contains the name of the month. After it again you need to insert a space (""). Cell A3 contains the year. The preposition "from" at the beginning of the formula can be removed or replaced with another.

For concatenation of elements, you can use not only the "&" symbol. Excel has a text function CONCATENATE. The function wizard is also used to call it. The function panel is shown in Fig. 6.11. Using this function, you can get a formula that is similar to the previous one, but more readable:

CONNECT ("from"; A16; A2; ""; A5; ""; AZ; "year.")

Date and time in words

The function displays the date in the copy, i.e. from the date "01.01.2009" makes the text "January 1, two thousand and nine". Why might this be necessary? For example, when it is necessary to write in the header (or wherever) of the report not just the date (01/01/2009), but the date in words (January 1, two thousand and nine). The result is available in Russian and English.

Calling a command through a standard dialog:

Call from MulTEx panel:

Sum / Search / Functions - Text - Date

Syntax:
\u003d CopyDate (I10; 1; 0; "RUS")

date(I10) - a date is indicated, or a reference to a cell with a date. Instead of a reference to a cell, you can immediately specify the date
\u003d RegistrationDate (09.01.2009; 1; 0; "RUS")

Time(1) is an optional argument. If specified, or assigned the value 1 or TRUE, in addition to the date, the time value is also displayed in the recipe ( 09.10.2009 18:12:05 - ninth october two thousand and nine eighteen hours twelve minutes five seconds).
If the time value is absent in the cell, or is equal to zero, then a zero value will be displayed in the copy ( zero hours zero minutes zero seconds ). The signatures of days, months, hours and minutes are declined (May 30, September 1, One hour, Two hours)

Signature only(0) is an optional argument. If specified, or assigned the value 1 or TRUE, only the name of the month is displayed in the recipe and the recipe for the year is added ( 09 October 2009 )

Language("RUS") is an optional argument. If "RUS" is not specified or specified, the text will be displayed in Russian. If you specify "EN" ("en", "En" - case is not important), then the text will be displayed in English. If the Time argument is 1 or TRUE, then the time will be written to the copy in the same language as the date.