How to change month in Excel. Examples of functions for working with dates: year, month and day in excel
The simplest and quick 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 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:
- 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.
- Using the TODAY() function.
- 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.
- 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.
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, which are represented as a sequence of numeric 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 the Hijri calendar was selected to display the entered date in the Excel table, 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.
| lesson 2| lesson 3 | lesson 4 | lesson 5
I think you already know from the last lesson that dates and times in Excel are stored in the form of ordinal numbers, the starting point of which is considered to be a certain January 0, 1900. Fortunately, in the cells we see not these numbers, but dates and times that are familiar to us, which can be stored in a variety of formats. In this lesson, you'll learn how to enter dates and times in Excel to get the formatting you want.
Entering dates and times in Excel
Dates and times in Excel can be entered as an ordinal number or a fraction of a day, but as you yourself understand, this is not entirely convenient. In addition, with this approach, you will have to apply a certain number format to the cell each time.
Excel offers several formats for entering temporary data. If you use this format, Excel will automatically convert the entered date (or time) to an ordinal number (a fraction of a day) and apply the format to the cell Dates(or Time) set by default.
The figure below shows a table of the date and time input options that Excel supports. The left column shows the values that need to be entered into the cell, and the right column shows the result of the conversion. It is worth noting that dates entered without specifying a year are assigned the current year, namely the one set in the settings of your operating system.
That's not all possible options, which Excel supports. But even these options will be plenty for you.
Some date display options shown in the right column may vary. This depends on your regional settings and the date and time display format in your operating system settings.
When working with cells in Microsoft Excel that contain a date or time, you have the opportunity to apply different formatting to them. For example, you can display in a cell only the day of the week, or only the month and year, or only the time.
You can access all possible formats on the tab Number dialog box Cell Format. In category Date The built-in date formats are:
In order to apply formatting to a cell, just select the desired format in the section Type and press OK. The required formatting will be applied. If the built-in number formats are not enough for you, you can use the category All formats. Here you can also find a lot of interesting things.
If none of the options suits you, then you can create a custom number format. It is quite easy to create if you use the built-in number formats as a template. To create a custom number format, follow these steps:
As you can see, everything is quite simple!
In this lesson, we learned how to customize the format for displaying dates and times in Microsoft Excel, and also looked at several useful options for entering them into a worksheet. In the next lesson we will talk about
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.
Initially Excel application for Windows is based on the 1900 date system because it improves compatibility with other spreadsheet programs developed for the MS-DOS operating system and Microsoft Windows, 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, 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; This 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 the section clock, language and region click .
Click the Language icon and regional standards.
In the dialog box region click the button Additional options.
Open the tab Date .
In the field
Click the button OK.
Windows 8
Search Search), enter in the search field Control Panel element Control Panel.
In the section clock, language and region click .
In the dialog box region click the button Additional options.
Open the tab Date .
In the 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 Additional options.
Open the tab Date .
In the 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 the 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 the section 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 Additional 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 the section clock, language and region click change date, time or number formats.
In the dialog box region click the button Additional 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 Regional and Language Options.
In the dialog box region click the button Additional 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 the section 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 date systems. 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 and earlier, subtract 1,462 from it. Example
Sheet1!$A$1-1462
More 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
If you are working with time-based data, you may need to insert a number of dates into the table. For example, in a sales tracking application, you might enter a series of dates for a quarter, making sure, of course, that you skip dates that fall on weekends.
Using AutoComplete
The most efficient way to enter multiple dates that doesn't require any formulas is to use the autofill feature. Enter the first date, and then drag the cell fill handle while right-clicking. Release the mouse button and select the context menu command Fill in by month(Fig. 108.1).
In some situations, you must enter two dates before you can use the AutoFill feature. For example, if you need to enter dates that are consecutive Mondays, you must enter the first two dates in the series. Then select both dates, right click and select in context menu item Fill by day.
Using Formulas
Another way to enter a series of dates is based on the use of formulas. The advantage of a formula (rather than an autocomplete function) is that you can change the first date and all the others will update automatically. Enter a starting date in a cell, and then use formulas (copying down the column) to create additional dates. For the following examples, assume that you entered the first date in the series in cell A1 and the formula in cell A2. You can then copy this formula down the column as many times as needed.
To generate a series of dates separated by seven days, use this formula: =A1+7 .
To create a series of dates separated by one month, use the following formula: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
To create a series of dates separated by one year, use this formula: =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)
To create a series that includes only weekdays (all days except Saturday and Sunday), use the following formula. It assumes that the date in cell A1 is not a holiday: =IF(WEEKDAY(A1)=6,A1+3,A1+1) . This formula returns serial number dates, so you need to format the cells to display the dates.
