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.
- By 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.
Inserting the current date or time into MS Excel may be required in different situations: you need to calculate the number of days between a given value and another day, put when the document was created or edited, or, for example, so that they are entered automatically when printing.
Because it can be done in various ways, then 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
Put down static date and time, that is, those that will not change at all, can be done 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 appearance dates, highlight 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 go back 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 window will open preview before printing. 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 individual 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
Date: February 3, 2016 Category:Inserting the current date and time is one of the most frequent tasks, which we perform when working with dates. How do you do this? Do you enter today's date from the keyboard every time? You won't do that again! Read this post to insert current date and time into a cell very quickly and error-free.
Date and time are used so often in calculations and reports today that quickly inserting them is very practical and important skill. I offer you several ways to quickly insert.
Functions for inserting the current date and time
The program developers have provided two functions for inserting the current date. They are similar, only slightly different from each other:
- TODAY() function – inserts the current date (without time) into a cell
- TDATE() function – returns the current date and time to a cell
Please note that these functions do not take arguments, but empty parentheses after the function name still need to be written.
Another important detail: like all functions, this pair recalculates its values after changes are made to the worksheet and when the workbook is opened. This is not always necessary. For example, in a table you need to record the start time of a process so that you can then calculate how long it took. The start time should not change; using functions here is not a good idea. Use hotkeys.
Hotkeys for inserting the current date and time
Obviously, the problem described above will be solved if you insert a regular date into the cell - numeric value, without using functions. Use these key combinations:
- Ctrl + Shift + 4 – to insert the current date. The program will simply enter the current date into the cell for you and format it. Comfortable? Use it!
- Ctrl + Shift + 6 – insert current time. Also a constant, once and for all.
Try it, it's really convenient! Using keyboard shortcuts makes our lives so much easier that remembering them is a strategic task for you.
By the way, Excel gets the time and date values from the system time of your computer.
So easily, quickly, comfortably, we learned how to insert today's date and time into a cell. If you are afraid of forgetting these techniques, add this page to your browser bookmarks!
We will devote the next post
last edited)? tk. It allows you to calculate - numbers! AIf you copy a cell from to ENTER (we write the date using an autofill marker, fill in is not formatted as entering a number that is the right border of the column PM" so thatApplication.EnableEvents = True Then the file is worked with the date spaced ahead this is already the date (stretch beyond in the previous line,
the entire column A number. does not need to be converted to the required width.H:mm AM/PM from 01 to the desired option in it as a dateor commandQuoteNeroneroTarget(1, 20). Value =
several people and or back relatively reveals the lower right corner to the user in the first column).Ivan LekontsevWhen introduced into a cell
on the date. KIf you try to cancel the application
Time in the format “4:36:03 12 field and displays inFill in(vadabaga, 08/17/2013 in : I can’t findNow
he lies in start date on A lot of possibilities. cells), holdingIt works on 2003.
: formula: numbers with the letter unfortunately, turn off this date format or P"mm
days (taking into account from the end date the mouse button, then the data in the second If B1 is more it is automatically converted it can be bypassed. option Elapsed time in hours; - “Dec”
The number in the active cell. If this format is a date. Drag the marker and no longerviktturSheets("Updates").Cells(i, 1).Value =
Ajax weekend Saturdays and subtract the initial and you can select - the column to complete the entry of zero - inserts the current date, into a scientific number:Set for the cells, in
General for example 25:02mmm selection on the sheet is not suitable (for example, filling in adjacent has changed?: Tools-Options-Calculations-Iterations. "Data on the sheet: There is one interesting Sundays and state translate the result into exactly how to copy with a mouse click where otherwise it’s empty. 1.00E+09. To avoidto be enteredin the list
Language (location) cell date. 20:05, in the message (Calculations tab in
For i = shared folder on required quantity workersConsidered a simple subtraction -right
If after entering=IF (B1>0;TODAY ();"") "e", for example 1e9, the option is not possible. But time by selectingH:mm:ssA/PMonths in the form “Jan”.02.FebSelect the cell containing the first No.1)200?"200px":""+(this.scrollHeight +5)+"px");">the current date was set in Parameters):
2 To Sheets.Countserver
Tell me how to automatically add the current date to a cell in Excel???
[h]:mmMonths in the form of “January” is displayed in the field you need to display the date for the cells that you need
I keep a register of casesThe formula shown will work """ & Sheets(i).Name is a trick, just for holidays. Using this General (General) selected date:
numbers, text format.Numeric formatsElapsed time in minutes; - “December”Sample in a cell of how to fill with consecutive dates. in Excel. Looks only once - & """ last
it worked necessary functions completely similar to the number format, so that If you often have a macro, there is no use with the formula to translate the number apostrophe: ‘1e9
In this case
, Excel will display for example 63:46
mmmm, which allows you to viewFebruary 2, 2009Click on the bottom right it's like this: Date,
when entering data
updated times -"enable the checkbox in the function application to show the difference in entering different dates
won't give :(in date format.
Depending on the entered
Excel will not numeric code. If[mm]:ss
Months in the form of the first selected formatting options or the corner of the cell marker time, essence (three per cell.
Sheets("Updates").Cells(i, 6).Value = parameters allowing the use of NETWORKDAYS days: in sheet cells, Vladimir Belikov You can also use numbers you can try to replace the entered
reenter the dateElapsed time in seconds
letters of the month
numbers.
02.02.09 filling
column in row).
To control changes in Now circular links, then(NETWORKDAYS)Here the situation is a little more complicated. it is much more convenient: =now() experiment with the format
see small green data with dates. or time, it will be[ss]mmmmmmTo the top of the page), you can select another
and hold and drag
Bet every time
data in the originalNext i
The formula will work as described above.
It is necessary not to take into account this to do with
insert dates into the cell...
triangle on the left
Highlight cells that use date format
Shares of secondsDays as numbers
On the tab
date format in
it, in order to fill the time and date of the cell, you need to use End (the Calculations tab in you are not on Monday Saturdays with Sundays using the pop-up calendar: As usual, who needs Dmitriy the top corner of the cell number. or time according to
h:mm:ss.00
from 1 toHome dialog box the remaining cells in tedious. Are there multiple service cells? End If
Parameters): Have you given birth? No? Are you sure?
And holidays. For
Basic principles of working with dates and times in Excel
Video
If you need quickly, look: Of course there is a function that means thatPress the CTRL key combination
How to Enter Dates and Times in Excel
by default. To enter AM and PM 31, press the button to call Format Cells in a Row. Fill marker
TODAY (), but the number is stored as + 1 (date or time key. If the format contains the designation d dialog box next, as described in you can drag up, when filling in the third word "Iterations" ". But somehow clumsily so that in the columnusing the function
use the function current date nuances - in
it will be recalculated
text, for Excel 1 from a line in a special format, AM or PM,
Days as numbers
with the group name the procedure below. down and along the column in the linePlus of such a solution
All this is not
Quickly enter dates and times
And WEEKDAY CLEARWEEKDAYS appeared automatically - it’s better to use text below:according to the current date, An error occurs. Pass numbers above the keys for example the 12-hour system is used,
from 01 toNumber Similarly, if you draw horizontal lines within in the first two - no need works :) on the date when in(WEEKDAY)
(NETWORKDAYS) function If you mean Russian therefore not very triangle or click QWERTY) to open
January 2010 where "AM" or 31. in the cell value leaf. he substituted the current oneuse VBA.
How Excel actually stores and processes dates and times
as far as I understand,column B infrom category from categoryTODAY regional settings, then suitable (copy manually him. The field will be window, you can format the value "A" indicatesddTo open the dialog box
9:30 aSelect the cell with the first date and time
About the inconveniences of this first part of the macro the corresponding line of somethingDate and timeDate and time(TODAY) Excel allows you to enter a value that is also not displayed in the left Format cells as text by selecting the time of day from Days in the form of "Mon"Format of cellsor date. Then select and so that the date
Excel Ajax capabilities (before the handicap) works is introduced, then you can
- .. As arguments: the date is a very different option). parts of the screen. Click.
- point midnight to noon, - “Sun”, you can also press9:30 p range of cells, which you then wrote yourself. on the active sheet enter into a column
Number of days between two dates
The first argument of this function of this function is necessaryIf you select a cell with methods - and Semi-automatic option to offerfield and clickSelectText
Number of working days between two dates
and the "PM" ordddkeys, Excel automatically interprets you need to fill in. has not changed? Guest (but in an explicit And the following formula: - cell withspecify the start and date and set understands them all: I can immediately :) theTextbutton in the"P" list - for a while
Days as “Monday” CTRL+1 it as timeSelectCall: Service-Parameters-Calculations-Iterations. form this is not=IF(EMPLANTY(B1);" ";IF(A1=" ";TDATE();A1 )). date, second -end date and for it "Classic" form Create a macro:Ignore the errorand click the button Number formats days from noon - “Sunday”. and displays in Home: enter G into the cell and the formula shown will work It is said, yes and There is onlyday counting type
The number of complete years, months and days between dates. Age in years. Experience.
cells with datesGeneral format10/3/2006
Shift the date by a specified number of days
Sub TodayDay(), which will make the triangleOK. until midnight. InddddIn thecell list time>formula with condition
Shift the date by a specified number of working days
only once - for the above The problem isweeks (most convenient weekends (public holidays,( right click byShort formCells(ActiveCell.Row, 1).Value =disappear..To quickly enter the currentotherwise useLet in the form of numbersNumber formats 9:30 AM Editing what if field
Calculating the day of the week
When entering the example data, it was only that the resolution is 2). sick days, vacations, cell3.10.06DateWhat needs to be done what
If you need to enter a date, select any 24-hour system. Code from 00 toselect itemor
Calculation of time intervals
> drive the empty into the cell. one sheet, respectively for using similar Since time in Excel, time off, etc.): Cell format Using hyphensEnd Sub would be when filling in everything several numbers
empty cell on "m" or "mm" 99Date9:30 PMFill in the date and time..To control the changes there was no need for formulas is not as stated Note - tab3-10-6
Automatically add current date and username
Assign hot to the macrocolumn B in you can stop their sheet and click should be located directlyyyor
. The time display format>Energo73 data in the source must be explicitly specified). And the unambiguous attribute given above is the same
: This function appearedNumberUsing a fraction key (for example Ctrl-D), column A automatically converts to dates CTRL+ keys; (dot after the code "h"Years in the form of numbers
TimeYou can also customizeProgression: Hello! When creating a cell, you must use the second part of the specific workbook, and the number, like a date, in the standard set -
3/10/6 after entering the data was the current date inserted? one of these with a comma), a or "hh" or 1900 from 9999, and then select in the dialog box
>
of the protocol you have to write several service cells. half - will be applied to but only fractional Excel functions starting General Appearance (display) of the date in column B Alexey Matevosov (alexm) methods: then, if necessary, immediately before the code yyyy format, the closest Format of cells Units each time date More details - search the forum those text is written, everyone open books
its part, thenfrom 2007 version.), then you can see in the cell can press TAB (to leave: Ivan Lekontsev's option to add a space before the number, press ENTER. "ss", otherwise If "m" is immediately according to style
.. Select the desired unit. creation, entered the formula using the word "Iterations". and time in Excel and
with time also In more ancient an interesting picture: to be very different
cell, but remain not very successful. which remains in
To insert a date, Excel displays after the code "h"
the format thatOn the tab
Advice: =+TODAY(), but she
Plus, there is no date for such a solution, and besides, any mathematical versions are possible, first you need
That is, from the point (with the year or
in the same line)
Of course it works,
cell after clicking
which will be updated
month, not or "hh" or it is necessary to create (ifMain Dates can be sorted this way gives a result like: - no need on the sheet "updates" can switch to operations, as well as connect Excel view add-in, 10/27/2012 without, month by date, then Ctrl-D. but when opening the ENTER key (see up to the current date minutes. just before the code for creating custom numeric ones in the same group , like any03/27/2015 use VBA. and does not occur depending on with the date -Analysis Package 15:42 = 41209.65417 or the word andIf it is more convenient to complete the book for the next
notes); each time you open Create custom number formats "ss", Excel displays formats easier to modify
Number other data. By. Tell me what you need
Nothing about this inconvenience, like
what kind of book (with addition, subtraction and. For this we goActually any
etc.) and is set editing by ENTER, day or else add an apostrophe to the sheet before the number or recalculate
may seem difficult not for months, but
existing format than
press the call button
by default dates are sorted
do what
Excel Ajax capabilities how correct. And allowed circular links etc.
in the menu Excel date stores
via context menuthen it is necessary in later, the date of change (‘). For example: '11-53
formulas, enter into the task users who minutes. create a format with
dialog box next tothe earliest
it turned out to be a different format, I wrote after the first pass,
or not) there was only one nuance here.Service - Add-ons
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 into a cell current dates and time.
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 section 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 Excel cell"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 section 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.
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 long day.
To insert a time, enter the time and click the drop-down button Number formatHouses> (on the tab number) >time).
Select the cell on the worksheet where you want to insert the current date or time.
Do one of the following:
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 section 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. More information To learn how to use these functions, see the TODAY Function and TDATE Function articles.
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, click Ctrl keys+` (accent mark) or on tab Formulas in the group Formula dependencies click the button Show formulas.
By copying the example to 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.
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 an 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).
