How to change month in excel. How to get the month from a date in excel (text and month function). Examples of using functions to process dates in Excel
Each line is a separate transaction. 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 extracting a month from a date, so that later it would be easy to sum up the data on them.
Method 1. Get month from date with MONTH function in Excel
We write down 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, SECOND, 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 with TEXT function in Excel
The syntax will be as follows
TEXT(value, format)
The value is a cell reference with the date
Format - to get months, you must use the capital letter "M". 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 see this clearly in the screenshot.
The easiest and fastest way to enter the current date or time in a cell is to press the hot key 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 value of the cell every day without user intervention.
How to put 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 simply type in the cell: "=TODAY()" and press ENTER.
Current date in cell:
If it is necessary that the value of not only the current date, but also the time is automatically updated in the cell, then it is better to use the “=TODATE()” 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 header and footer parameters. The advantage of this method is that the current date and time are placed on all pages at once.
- Using the TODAY() function.
- Using the hot key combination CTRL+; – to set the current date and CTRL+SHIFT+; – to set the current time. The disadvantage is that in this method the cell value will not be automatically updated to the current indicators when the document is opened. But in some cases, the lack of data is an advantage.
- 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 header and footer allows us to number all 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 page numbers. You can also add a place for the signature of the person responsible for the report. For example, let's edit the lower left part of the page in the header and footer area.
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 in the project take to complete, or what is the average revenue for the fiscal quarter? Correct entry of dates is a prerequisite for 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 calculator program are complex, you should be as 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 consecutive numbers, which are called consecutive values. For example, in Excel for Windows, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because that's 39,448 days after January 1, 1900.
Excel stores times as decimals because time is treated as part of a day. A decimal number is a value between 0 (zero) and 0.99999999 representing the 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 to, subtracted from, 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 the general format.
About 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 and Microsoft Windows operating systems, and so it 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 the last period date for each date system and the sequential value associated with each date.
Since the two date systems use different starting days, the same date is represented by different ordinal 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 1462 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 1462 days less than the serial value of the same date dates in the 1900 date system. 1462 days equals 4 years and one day (including one leap day).
Change how a two-digit year is interpreted
Important: For year values to be interpreted correctly, enter four digits (for example, 2001, not 01). If you enter four-digit years, Excel will not interpret that century.
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 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 how the two-digit year is interpreted for all installed Windows programs.
Windows 10
Control Panel and then select Control Panel.
In chapter hours, language and region click .
Click the Language icon and regional standards.
In the dialog box region press the button Extra options.
Open a tab the date .
In field
Click the button OK.
Windows 8
Search Search), enter in the search field control panel element Control Panel.
In chapter hours, language and region click .
In the dialog box region press the button Extra options.
Open a tab the date .
In field after entering two digits of the year, interpret it as the year between frames by 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 a tab the date .
In field after entering two digits of the year, interpret it as the year between frames by 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 a workbook, the dates are formatted to display two digits for the year. 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 " Cell Format" "(on the tab home in a group number press the button call dialog box).
Windows 10
In the search box on the taskbar, type Control Panel and then select Control Panel.
In chapter hours, 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 a tab the date .
Shortlisted Date format
Click the button OK.
Windows 8
Swipe in from the right edge of the screen, press the button Search(or, if you're using a mouse, move your mouse pointer to the top right corner of the screen, move it down, and click the button Search), enter in the search field control panel element, and then press or Select Control Panel.
In chapter hours, language and region click change date, time or number formats.
In the dialog box region press the button Extra options.
Open a tab the date .
Shortlisted Date format choose 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 an item language and regional standards.
In the dialog box region press the button Extra options.
Open a tab the date .
Shortlisted Date format choose a format that uses four digits for the year (yyyy).
Click the button OK.
Change the date system in Excel
The date system automatically changes when you open a document created on a different platform. For example, if you're working in Excel and you've opened a document created in Excel for Mac, the " date system 1904" installed automatically.
You can change the date system by doing the following:
Select File > Options > Additionally.
In chapter when calculating this book select the book you want, and then check or uncheck use 1904 date system .
Problem: Date issues occur between workbooks using different date systems
You may run into problems when copying and pasting dates, or when creating external links between books based on two different date systems. Dates can be shown up to four years earlier or later than the expected date. These issues 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. 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 consist of July 4, 2003, which is 1,462 days earlier . For general information, see Date systems in Excel.
Correction of error when copying and pasting
To set the date to four years and one day later, add 1462 to it. Example
Sheet1!$A$1+1462
To set the date to four years or earlier, subtract 1,462 from it. Example
Sheet1!$A$1-1462
additional information
You can always ask the Excel Tech Community a question, ask for help in the Answers community, or suggest a new feature or improvement on the Excel User Voice website
Each transaction is carried out at some time or period, and then tied to a specific date. In Excel, dates are whole numbers converted. 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 successive 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 transactions with dates 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 practice for any kind of sorting. After all, in such cases, if the data is shuffled incorrectly, we always have the opportunity to return to the original form of the table. For this:
As a result, the column will automatically be 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 holding only the right mouse button, 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 automatically be filled with a sequence of numbers, similar to the first autocomplete method.
Now it's the turn to create auxiliary columns, which will significantly help to sort the list of transactions with high quality. You need to follow a few simple steps:
- Cells D1, E1, F1 fill in the names of the headers: "Year", "Month", "Day".
- According to 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 dates. Let's do a test sort first to see how 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, in order to reset the sorting and bring the table data back to its original form, go to any cell in the “No. n / n” 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 the complex sorting of dates according to several conditions. The task is as follows - transactions must be sorted in the following order:
- Years ascending.
- Months in the period of certain years - in descending order.
- Days in periods of certain months - in descending order.
Way to implement 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 options in drop-down lists such as Oldest to Newest and Newest to Oldest. But in practice, when working with large amounts of data, the result does not always live up to expectations. Since dates are integers for Excel, it is safer and more efficient to sort them using the method described in this article.
Let's create sequences of dates and times of various types:01/01/09, 02/01/09, 03/01/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 the article ), then the approaches for forming sequences described 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 =DATE(B2,ROW(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 into the cell A2 entered value 01.01.09 . Select a cell A2 . Holding down the right mouse button, 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 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/ General/ 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.
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 the ideas from the article.
Using the tool, you can create sequences of working days only. And also in increments of month and year.
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 (after selecting them) copy down fill marker. Cells will contain text values. For cells to contain dates, use the formula =DATE($G$16,(ROW(A2)-ROW($A$1))*3) Sequence is assumed to start at cell G16 , the formula must be entered in a cell G17 (see example file).
time sequence 09:00, 10:00, 11:00, ... can be generated using . Let into the cell A2 entered value 09 :00 . Select a cell A2 . Let's copy fill marker, value from A2 in the cells below. The sequence will be generated.
If you want to generate a time sequence in increments of 15 minutes ( 09:00, 09:15, 09:30, ... ), then you can use the formula =B15+1/24/60*15 (It is assumed that the sequence starts from the 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.