Automatic setting of date and time in excel. How to quickly insert current date and time in Excel

Excel Program from Microsoft Corporation is powerful processor, which makes it easier for many users to work with large amounts of tabular data on a daily basis. However, even experienced specialists who have been working with this program for many years sometimes get lost in front of the need to insert the current date and time into a worksheet cell. This is mainly due to the fact that the "Date" function, which would be logical to use to get desired values, performs a completely different task. In this article, we will tell you about the different types of dates used in Excel, how to get them, and how to use them.

Date types

Excel considers two options for the "Today's Date" value. The first one is a fixed value current date and time stored on personal computer user. Once entered into a worksheet, the value will not change regardless of the change in the actual date and time. When might this option be needed? There are many answers, for example, when we store the date an employee was hired or fired, we enter the date the goods arrived at the warehouse. These values ​​should be static, because they do not change over time.

The second variant of the "Date current" value is dynamic, changeable, updated. The most common use of this option is as a date and time value in the corner of a worksheet, as part of the caption "Today May 14, 2017". This option widely used in formulas, for example, to calculate how many days have passed since a certain date. Thus, the personnel officer can find out if the probationary period of an employee has come to an end, and the warehouse worker will check if the batch of goods has expired.

Of course, dynamic date and time in Excel is very useful, but there is a very important nuance: there is no continuous updating of these values. The entered value will change when the workbook is reopened, when a macro is executed, and when various formulas are calculated. If you just leave the workbook open and do none of the above, the date and time will remain the same as you entered once. But as soon as you reopen the book or recalculate the formula, the values ​​will be updated.

Consider how to insert these two types of dates in Excel.

Date fixed, not updated

The Excel value "Today's date" can be obtained by simply pressing Ctrl and ";". Just stand on the desired cell in the worksheet and immediately, while holding down the Ctrl key, press the semicolon sign. The current date will appear in the cell in a short format, for example, 05/14/2017.

To insert a time, perform a similar operation: hold down the Ctrl key, press and hold Shift, and also press a semicolon. All. The current time is inserted in the format "hours:minutes".

As you can see, it is very easy to insert the current fixed format date in Excel. The only inconvenience is that on most keyboards that do not have additional buttons, you have to switch to the English layout.

Date is dynamic, updated

The current date in the updated form is set in Excel using formulas. Option two:

• The formula "=TODAY()" allows you to get the values ​​of the current date.
• The formula "=TODATE()" allows you to get the current date and time.

These functions have no arguments, and the values ​​obtained by using them are updated each time the formulas are recalculated/copied/dragged or the sheet is reopened.

To enter a date in Excel, use the "/" or "-" characters. To enter a time, use ":" (colon). You can enter date and time in one cell.

Note: The figures in this article show dates in the format of the United States and a number of other countries, where months come first, and then days. The format type depends on your regional . Here you can get Additional information about date and time formats.

YEAR, MONTH, DAY

To get the year of a date use the function YEAR(YEAR):

YEAR(A1)
=YEAR(A1)

Note: Use features MONTH(MONTH) and DAY(DAY) to get the month and day of the date.

DATE function

Note: Function DATE(DATE) takes three arguments: year, month, and day. Excel knows that 6+2=8 is August. As you know, there are 31 days in August, so the program moves on to the next month (August 23 + 9 days = September 1).

Current date and time

To get the current date and time use the function TODAY(NOW):

NOW()
=NOW()

Note: Use the function TODAY(TODAY) to get only the current date. To get only the current time, use the following formula (don't forget to apply the time format to the cell):

TODAY()-TODAY()
=NOW()-TODAY()

HOUR, MINUTES, SECONDS

To return the clock, use the function HOUR(HOUR):

HOUR(A1)
=HOUR(A1)

Note: Use features MINUTES(MINUTE) and SECONDS(SECOND) to return minutes and seconds respectively.

TIME function

To add hours, minutes and/or seconds, use the function TIME(TIME):

TIME(HOUR(A1)+2;MINUTES(A1)+10;SECONDS(A1)+70)
=TIME(HOUR(A1)+2,MINUTE(A1)+10,SECOND(A1)+70)

Note: Excel will add 2 hours, 10+1 = 11 minutes and 70-60 = 10 seconds.

To work with dates in Excel, the category "Date and time" is defined in the section with functions. Let's take a look at the most common features in this category.

How Excel handles time

Excel treats the date and time as a normal number. The spreadsheet converts such data by equating the day to one. As a result, the time value is a fraction of a unit. For example, 12.00 is 0.5.

The spreadsheet converts the date value to a number equal to the number of days from January 1, 1900 (so the developers decided) to the given date. For example, when converting the date 04/13/1987, the number 31880 is obtained. That is, 31,880 days have passed from 1/1/1900.

This principle underlies the calculation of time data. To find the number of days between two dates, it is enough to subtract the earlier one from the later time period.

DATE function example

Constructing a date value by composing it from individual number elements.

Syntax: year; month, day.

All arguments are required. They can be set as numbers or cell references with corresponding numerical data: for a year, from 1900 to 9999; for a month - from 1 to 12; for the day - from 1 to 31.

If the Day argument is set to a larger number (than the number of days in the specified month), then the extra days will roll over to the next month. For example, specifying 32 days for December, we will get January 1 as a result.

An example of using the function:

Let's set large quantity days for June:

Examples of using cell references as arguments:

RAZDAT function in Excel

Returns the difference between two dates.

Arguments:

• start date;
• final date;
• code denoting the units of calculation (days, months, years, etc.).

Ways to measure intervals between given dates:

• to display the result in days - "d";
• in months - "m";
• in years - "y";
• in months excluding years - "ym";
• in days, excluding months and years - "md";
• in days excluding years - "yd".

In some versions of Excel, when using the last two arguments ("md", "yd"), the function may return an erroneous value. It is better to use alternative formulas.

Examples of the operation of the RAZDAT function:

In the version of Excel 2007, this function is not in the reference, but it works. Although it is better to check the results, because errors are possible.

YEAR function in Excel

Returns the year as an integer (from 1900 to 9999) that corresponds to the given date. There is only one argument in the function structure - the date in number format. The argument must be entered using the DATE function or represent the result of evaluating other formulas.

An example of using the YEAR function:

MONTH function in Excel: example

Returns the month as an integer (from 1 to 12) for a given date in numeric format. The argument is the date of the month to display, in numeric format. Dates in text format function handles incorrectly.

Examples of using the MONTH function:

Examples of DAY, WEEKDAY, and WEEKDAY functions in Excel

Returns the day as an integer (between 1 and 31) for a given date in numeric format. The argument is the date of the day to be found, in numeric format.

To return the ordinal number of the day of the week for specified date, you can apply the WEEKDAY function:

By default, the function considers Sunday as the first day of the week.

To display the serial number of the week for the specified date, use the WEEKNOME function:

May 24, 2015 falls on the 22nd week of the year. The week starts on Sunday (default).

The number 2 is specified as the second argument. Therefore, the formula assumes that the week starts on Monday (the second day of the week).

To specify the current date, use the TODAY function (it has no arguments). To display the current time and date, use the NOW() function.

One of the most demanded groups of operators when working with Excel spreadsheets are functions of date and time. It is with their help that you can carry out various manipulations with temporary data. Date and time are often put down when registering various event logs in Excel. To process such data is the main task of the above operators. Let's see where you can find this group of functions in the program interface, and how to work with the most popular formulas of this block.

The date and time function group is responsible for handling data presented in date or time format. Currently, there are more than 20 operators in Excel that are included in this block of formulas. With the release of new versions of Excel, their number is constantly increasing.

Any function can be entered manually if you know its syntax, but for most users, especially inexperienced or with no higher than average knowledge, it is much easier to enter commands through the graphical shell presented Function Wizard followed by moving to the arguments window.

Besides, Function Wizard can be activated by selecting a cell on the sheet and pressing the key combination Shift+F3. There is also the option to switch to the tab "Formulas", where on the ribbon in the tool settings group "Function Library" button should be clicked "Insert Function".

It is possible to move to the window of arguments of a specific formula from the group "Date and time" without activating the main window of the Function Wizard. To do this, move to the tab "Formulas". Clicking on the button "Date and time". It is located on the ribbon in the tool group "Function Library". The list of available operators in this category is activated. Choose the one that is needed to complete the task. After that, it moves to the arguments window.

DATE

One of the simplest, but at the same time popular functions of this group is the operator DATE. It displays the given date in numerical form in the cell where the formula itself is placed.

His arguments are "Year", "Month" And "Day". A feature of data processing is that the function works only with a time interval not earlier than 1900. Therefore, if as an argument in the field "Year" set, for example, 1898, then the operator will display an incorrect value in the cell. Naturally, as arguments "Month" And "Day" the numbers are from 1 to 12 and from 1 to 31, respectively. References to cells containing the corresponding data can also act as arguments.

To manually enter a formula, use the following syntax:

DATE(Year;Month;Day)

Operators close to this function in value YEAR, MONTH And DAY. They display in the cell the value corresponding to their name and have a single argument of the same name.

RAZDAT

A kind of unique function is the operator RAZDAT. It calculates the difference between two dates. Its peculiarity is that this operator is not in the list of formulas Function Wizards, which means that its values ​​always have to be entered not through the graphical interface, but manually, adhering to the following syntax:

DISTRIBUTION(start_date, end_date, unit)

It is clear from the context that as arguments "start date" And "Final date" the dates appear, the difference between which you want to calculate. But as an argument "Unit" there is a specific unit of measurement of this difference:

• Year(y);
• Month(m);
• Day(d);
• Difference in months (YM);
• Difference in days excluding years (YD);
• Difference in days excluding months and years (MD).

NETWORKDAYS

Unlike the previous operator, the formula NETWORKDAYS listed Function Wizards. Its task is to count the number of working days between two dates, which are given as arguments. In addition, there is another argument - "Holidays". This argument is optional. It indicates the number of holidays for the period under study. These days are also subtracted from the total calculation. The formula calculates the number of all days between two dates, except for Saturday, Sunday, and those days that the user specifies as public holidays. The arguments can be both the dates themselves and references to the cells in which they are contained.

The syntax looks like this:

NETWORKDAYS(start_date, end_date, [holidays])

TODAY

Operator TODAY interesting in that it has no arguments. It displays the current date and time set on the computer in the cell. Note that this value will not be updated automatically. It will remain fixed at the time the function is created until it is recalculated. To recalculate, just select the cell containing the function, place the cursor in the formula bar and click on the button Enter on keyboard. In addition, periodic document recalculation can be enabled in its settings. Syntax TODAY such:

TODAY

The operator is very similar to the previous function in its capabilities. TODAY. It also has no arguments. But the cell does not display a snapshot of the date and time, but only one current date. The syntax is also very simple:

TODAY()

This function, like the previous one, requires recalculation for updating. Recalculation is performed in exactly the same way.

TIME

The main task of the function TIME is the output to the specified cell specified by the time arguments. The arguments to this function are hours, minutes, and seconds. They can be specified both as numeric values ​​and as links pointing to the cells in which these values ​​are stored. This function is very similar to the operator DATE, only in contrast to it displays the given indicators of time. Argument value "Watch" can be set in the range from 0 to 23, and the minute and second arguments can be from 0 to 59. The syntax is:

TIME(Hours, Minutes, Seconds)

In addition, individual functions can be called close to this operator. HOUR, MINUTES And SECONDS. They display the value of the time indicator corresponding to the name, which is specified by a single argument of the same name.

DATEVALUE

Function DATEVALUE very specific. It is not intended for people, but for the program. Its task is to convert a date entry in its usual form into a single numeric expression available for calculations in Excel. The only argument to this function is the date as text. Moreover, as in the case of the argument DATE, only values ​​after 1900 are handled correctly. The syntax looks like this:

DATEVALUE (date_as_text)

DAYWEEK

Operator task DAYWEEK– display in the specified cell the value of the day of the week for the specified date. But the formula does not display the text name of the day, but its ordinal number. Moreover, the starting point of the first day of the week is set in the field "Type". For example, if you set this field to "1", then Sunday will be considered the first day of the week if "2"- Monday, etc. But this is not a required argument, if the field is not filled in, then it is considered that the countdown starts from Sunday. The second argument is the date itself in numeric format, the ordinal number of the day of which you want to set. The syntax looks like this:

DAYWEEK(Date_in_number_format, [Type])

NOMWEEK

The purpose of the operator NOMWEEK is an indication in a given cell of the week number for the input date. The arguments are the actual date and the type of the return value. If everything is clear with the first argument, then the second requires additional explanation. The fact is that in many European countries, according to ISO 8601 standards, the first week of the year is the week on which the first Thursday falls. If you want to apply this system counting, then in the type field you need to put a number "2". If you prefer the usual reference system, where the first week of the year is the one that falls on January 1, then you need to put a number "1" or leave the field blank. The syntax for the function is:

NUMWEEK(date, [type])

SHARE YEAR

Operator SHARE YEAR makes a shared calculation of the segment of the year between two dates for the whole year. The arguments of this function are these two dates, which are the boundaries of the period. In addition, this function has an optional argument "Basis". It specifies how the day is calculated. By default, if no value is specified, the US calculation method is taken. In most cases, it is just right, so most often this argument does not need to be filled in at all. The syntax takes the following form:

FRACTION(start_date, end_date, [basis])

We have gone through only the main operators that make up the group of functions "Date and time" in Excel. In addition, there are more than a dozen other operators of the same group. As you can see, even the functions we have described can make it much easier for users to work with values ​​in formats such as date and time. These elements allow you to automate some calculations. For example, by entering the current date or time in the specified cell. Without mastering the management of these functions, one cannot speak of a good knowledge of Excel.

The simplest and fast way enter the current date or time in the cell - this 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 just 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:

1. 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.
2. Using the TODAY() function.
3. Using the hot key combination CTRL+; – to set the current date and CTRL+SHIFT+; – to set the current time. The disadvantage is this method the value of the cell will not be automatically updated to the current values ​​when the document is opened. But in some cases, the lack of data is an advantage.
4. WITH 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 left lower part pages in the header area.

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 can enter a series of dates for a quarter, making sure to skip dates that fall on weekends, of course.

Using autocomplete

Most effective method enter multiple dates, which does not require any formulas - using the function automatic filling. Enter the first date, and then drag the cell fill handle while pressing the right mouse button. 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 using the autocomplete 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 the formula (rather than the autocomplete function) is that you can change the first date and all the others will be updated automatically. Enter a start date in a cell, and then use formulas (copying down the column) to create additional dates. In the following examples, suppose you enter 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 dates.