Inserting a date in Excel. Inserts the current date and time into a cell. How Excel handles time

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 Fill by Day from the context menu.

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 the serial number of the date, so you need to format the cells to display the dates.

Let's say you just want to enter the current date and time into the event log. Or perhaps you want to automatically display the current date and time in a cell every time you recalculate your formulas. There are several ways to insert the current date and time into a cell.

    • To insert the current date, press CTRL+; (semicolon).

      To insert the current time, press CTRL+SHIFT+; (semicolon).

      To paste the current date and time, press CTRL+; (semicolon), then the SPACEBAR key, and after that - CTRL+SHIFT+; (semicolon).

Cell Format. Then in the dialog box Cell Format on the tab number In chapter Category select item date or time and then in the list type OK.

Insert a fixed date or time value into an Excel cell

A fixed value on a sheet does not change when the sheet is recalculated or opened. When you press CTRL+; To insert the current date into a cell, Excel "takes a snapshot" of the current date, which is inserted into the cell. Since the cell value does not change, it is considered static.

Changing the date and time format

To change the date or time format, right-click the cell and select Cell Format. Then in the dialog box Cell Format on the tab number In chapter Category select item date or time and then in the list type select the type and click the button OK.

Insert a fixed date or time value into an Excel cell

A fixed value on a sheet does not change when the sheet is recalculated or opened. When you press CTRL+; To insert the current date into a cell, Excel "takes a snapshot" of the current date, which is inserted into the cell. Since the cell value does not change, it is considered static.

    Select the cell on the worksheet where you want to insert the current date or time.

    Do one of the following:

    • To insert a date, enter the date (for example, 2/2) and click the drop-down button Number formatHouses> (on the tab number) >Short date or a long day.

      To insert a time, enter the time and click the drop-down button Number formatHouses> (on the tab number) >time).

Changing the date and time format

To change the date or time format, right-click the cell and select Numeric Format. Then in the dialog box Number format In chapter Category select value date or time and then in the list type select the type and click the button OK.

Inserting an updated date or time value

A date or time that is updated when a worksheet is recalculated or a workbook is opened is considered "dynamic" rather than static. The most common way to get a dynamic date or time in a cell on a worksheet is to use a worksheet function.

To insert an updated current date or time, use the functions TODAY And TDATE, as shown in the example below. For more information on using these functions, see the TODAY Function and the TDATE Function.

For example:

    Select the text in the table above and press CTRL+C.

    On a blank worksheet, select cell a1 once, and then press CTRL+V. If you're working in Excel for the web, repeat the copy and paste for each cell in the example.

    Important: For the example to work correctly, it must be inserted into cell A1.

    To switch between viewing the results and viewing the formulas that return those results, press Ctrl+` (accent mark) or on the tab Formulas in Group Formula dependencies click the button Show formulas.

By copying the example onto a blank sheet, you can customize it to suit your needs.

Note: Results returned by functions TODAY And TDATE, are changed only when the worksheet is recalculated or when a macro containing this function is executed. Cells containing these functions are not continually updated. The computer's system clock is used as date and time values.

additional information

You can always ask a question in the Excel Tech Community, ask for help in the Answers community, or suggest a new feature or improvement on the Excel User Voice website.

Note: This page has been automatically translated and may contain inaccuracies and grammatical errors. It is important to us that this article is useful to you. Was the information useful? For convenience also (in English).

Inserting the current date or time into MS Excel may be required in various situations: you need to calculate the number of days between a given value and another day, enter when the document was created or edited, or, for example, so that they are entered automatically when printing.

Since this can be done in various ways, let's look at them in this article. I’ll show you how to set the date and time in Excel, which will not change, and those that will be updated every time the document is opened or recalculated.

Hotkeys

You can set a static date and time, that is, ones that will not change at all, using a keyboard shortcut. Select the desired cell and press “Ctrl+Shift+;” - time will be displayed. If you press “Ctrl+;” – the date will appear. The semicolon symbol is the one that is on the same button as the Russian letter “Zh”.

In the example, I displayed them in different cells. If necessary, you can set them into one: press one combination first, add a couple of spaces and press the second.

Please note that the time that is set on the system clock will be displayed, this is the one located at the bottom right of the screen.

Using Formulas

To ensure that the entered data is regularly updated, depending on the current date and time values, that is, dynamic, you can use formulas.

Select the desired cell (A4). Go to the tab with the appropriate name at the top, expand the “Date and Time” list and select in it.

In the window with arguments, click “OK”, since this function does not have them.

As a result, both time and date will be displayed in A4. When recalculating a sheet or cell, the value will be updated.

To display only the date, use the function, it is also in the list.

She has no arguments, so we close the small window.

Now I have today's date written in A5.

I wrote about various options for using the TODAY function in Excel in a separate article, which you can read by following the link.

Inserting a footer

If you want the date and current time to appear consistently on every sheet you print, then you can add them in the header and footer area. Moreover, you can choose where they will be displayed: top or bottom, right or left.

Open it at the top and in the “Options” group, click on the small arrow in the corner on the right.

In the next window, go to the tab we need at the top, and then click on the button, depending on what kind of footer you want to create, I will make a footer.

A window like this will open. There will be three areas down here, put italics where the date and time should be. I'll put them on the right. Then click on the button either with the calendar, to insert today's date, or with the clock. I added a few spaces between the inserted characters to separate them from each other.

It is not necessary to insert them one after another; add either one thing or add some text. For example:

Today's date: &[Date]
Time: &[Time]

In order to change the appearance of the date, select everything you wrote and click on the button with the drawn “A”.

In the window that opens, you can select a different font, size of numbers and letters, and much more. Then click "OK".

Save the changes by clicking "OK".

Let's return to this window. It shows how the added data is displayed. You can press "OK". If you want to see how everything will look on the sheet with other data, click on the "View" button.

A print preview window will open. Here, flip through the pages and make sure that the date is inserted as you need. To return to the sheet in Excel, click on any tab at the top, for example, “Data”.

You can also see what the date will look like differently. Open the “View” tab at the top, and then click on the button. After this, the worksheet will be divided into separate pages. At the bottom or at the top, depending on what you chose, today's date will appear.

By clicking on it, you can edit it. The tab contains items that you can add to the footer. It is among them that you will find the necessary buttons “Current date” and “Current time”.

If you often have to work with dates, then read the article: date and time functions in Excel, it describes and shows with examples how to use the functions that are included in this category.

In these ways, you can add the current date or time to a cell or footer in Excel.

Rate this article:

(2 ratings, average: 5,00 out of 5)

Webmaster. Higher education with a degree in Information Security. Author of most articles and computer literacy lessons

When working with documents in Microsoft Excel, sometimes you need to add time or date, or both. Unfortunately, not all users know about special tools implemented in Excel that can quickly solve this problem. In this article, we'll look at how to insert a time or date in Excel. Let's figure it out. Go!

Additional functionality is used to add data

In order to solve the problem, you need to use one of two functions of the program: “Current date” or “Today”. Please note that the above tools are not suitable for cases where the number must be fixed, that is, not change every new day.

First, determine the cell you will be working with. Next, go to the “Formulas” tab. In the “Function Library” block, click “Date and Time”. Then select “Today” and click on the “OK” button. The area you select will then contain the current value, which will change every day. The same can be done using the key combination Ctrl+Shift+4, in some cases Ctrl+F, or enter “=TODAY()” in the special field for formulas.

To add time, in the same section of the toolbar, click on the “Time” item. Please note that all values ​​will be set in accordance with information from your computer's calendar and clock.

The values ​​you set will be updated every time you open the workbook, but if you need to update the data while working with the workbook, go to the “Formulas” tab and click the “Recalculation” button located in the “Calculations” tool ribbon block. You can also use the “Calculation Options” button, then select “Automatic”. There is a simpler and faster option - use the F9 key on your keyboard.

If you want only hours to be displayed in the field, open the “Format Cells” window. In the “Number Formats” section, select “Time”, and in the “Type” section, click on the appropriate clock display option. An alternative way is to use the key combination Ctrl+Shift+6.

As you can see, this problem can be solved quite simply. Having figured out how to insert a calendar into Excel using special functions, you will be able to work with documents and tables faster and more efficiently. Write in the comments whether the article was useful to you, and also ask questions on the topic that you might have while reading the article.

One of the most popular groups of operators when working with Excel tables are date and time functions. It is with their help that you can carry out various manipulations with temporary data. Date and time are often entered when creating various event logs in Excel. Processing such data is the main task of the above operators. Let's figure out 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 processing data represented in date or time format. Currently, Excel has more than 20 operators that are included in this formula block. 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 less than average knowledge, it is much easier to enter commands through the graphical shell provided 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 go to the tab "Formulas", where on the ribbon in the tool settings group "Function Library" you should click on the button "Insert Function".

It is possible to move to the argument window for a specific formula from a group "Date and time" without activating the main Function Wizard window. To do this, move to the tab "Formulas". Click on the button "Date and time". It is located on the ribbon in the tools group "Function Library". The list of available operators in this category is activated. We choose the one that is needed to complete the task. After this, you are moved 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 located.

His arguments are "Year", "Month" And "Day". A peculiarity of data processing is that the function only works with a time period 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" numbers are from 1 to 12 and from 1 to 31, respectively. Links to cells containing the corresponding data can also be used as arguments.

To manually enter a formula, use the following syntax:

DATE(Year;Month;Day)

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

RAZNDAT

A kind of unique function is the operator RAZNDAT. 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:

RAZNDAT(start_date, end_date, unit)

From the context it is clear that as arguments "Start date" And "Final date" the dates appear, the difference between which needs to be calculated. But as an argument "Unit" a specific unit of measurement for this difference appears:

  • 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 presented in the list Function Wizards. Its task is to count the number of working days between two dates given as arguments. In addition, there is another argument - "Holidays". This argument is optional. It indicates the number of holidays during the period under study. These days are also deducted from the total calculation. The formula calculates the number of all days between two dates, except Saturday, Sunday and those days specified by the user as holidays. The arguments can be either dates themselves or references to the cells that contain them.

The syntax looks like this:

NETWORKDAYS(start_date, end_date, [holidays])

TDATE

Operator TDATE interesting because it has no arguments. It displays the current date and time set on the computer in a cell. It should be noted 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 recalculation of the document can be enabled in its settings. Syntax TDATE such:

TODAY

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

TODAY()

This function, just like the previous one, requires recalculation to be updated. Recalculation is performed in exactly the same way.

TIME

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

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 record in its usual form into a single numeric expression that can be calculated 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 processed correctly. The syntax looks like this:

DATEVALUE (date_as_text)

WEEKDAY

Operator task WEEKDAY– display in the specified cell the value of the day of the week for a given date. But the formula does not display the text name of the day, but its serial number. Moreover, the starting point of the first day of the week is specified in the field "Type". So, 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 actual date in numeric format, the day number of which needs to be set. The syntax looks like this:

WEEKDAY(Date_in_numeric_format, [Type])

WEEK NUMBER

The purpose of the operator WEEK NUMBER is the 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 considered to be the week on which the first Thursday falls. If you want to use this reference system, then you need to put a number in the type field "2". If you prefer the usual system of counting, where the first week of the year is considered to be the one on which January 1st falls, then you need to put a number "1" or leave the field blank. The syntax for the function is:

WEEKNumber(date,[type])

PERCENTAGE OF THE YEAR

Operator PERCENTAGE OF THE YEAR makes a fractional calculation of the segment of the year concluded between two dates for the entire year. The arguments to 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 American calculation method is used. In most cases, it is just right, so most often this argument does not need to be filled in at all. The syntax looks like this:

FRACTION OF YEAR(start_date,end_date,[basis])

We only went through 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 we can see, even the functions we have described can greatly facilitate users’ work with values ​​of formats such as date and time. These elements allow you to automate some calculations. For example, by entering the current date or time into a specified cell. Without mastering the management of these functions, one cannot speak of a good knowledge of Excel.