Ask Your Question

Automated timestamps, Please help. Thanks

asked 2016-04-11 12:29:54 +0200

CCGirl gravatar image

updated 2016-04-11 12:31:04 +0200

Hello I was wondering if someone could help me make a row where for example if i type something in B1, A1 automatically inserts the date so i dont need to put it manually. I read on forums but i don't understand how to do it. Here is a post i found That Lupo made 2 examples of it. i want it like that but i donno how to set it up for my own file. i can't upload any file now . but if you scroll down on that page, he attached 2 examples. Thats how i want my file to be like. Could someone please help me make something like that? Thank you so much

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2018-02-04 22:40:19 +0200

updated 2018-02-05 01:54:16 +0200

To put date or a time in quickly. Hold down Ctrl+; that will put in today's date. Ctrl+Shift+; will put in the time. You still have to put it in, but until you find something this will be easy and quick. I really do no believe a spread sheet only will put a time in automatically AND STAY THAT WAY, it will change EACH days to the present, or current day! So then, in a weeks time, all dates will be same. A variable is needed for that which a spread sheets do not have.

edit flag offensive delete link more

answered 2018-01-27 02:09:51 +0200

Hi there, I checked one of the files and the formula in cell "a11" is as follows : =IF(B11="","",IF(A11="",NOW(),A11))

A quick overview of the if statement from the help menu!

IF Specifies a logical test to be performed. Syntax IF(Test; ThenValue; OtherwiseValue) Test is any value or expression that can be TRUE or FALSE. ThenValue (optional) is the value that is returned if the logical test is TRUE.

So to breakdown the formula in cell a11,

If the value in b11 is blank then the value in a11 is blank. If there is a value in b11, it then checks the value in a11, if blank it then updates "a11" with the current date / time, if there is a value already in it A11 it doesn't change anything. Just to date / time printed depends on how the cell is formatted.

The only thing that you would need to update in your file is to change the row number (in this case 11) with the row(s) to match the row with the data and copy down the formula and possibly the formatting of the cell.

Hope this helps. Any questions feel free to post again.

edit flag offensive delete link more


This normally results in Err:522 if B11 is not empty, because of the recursion / circular reference involved (A11 asks itself). You'd have to allow iterations to make the error disappear, then the result will be 0 first and when re-entering a value to B11 the date will show. However, usually one does not want to enable iterations as it may lead to unexpected results when formulas are entered that unintended lead to circular references.

erAck gravatar imageerAck ( 2018-02-05 13:18:44 +0200 )edit

answered 2018-02-04 23:24:14 +0200

librebel gravatar image

Hello @CCGirl,

To automatically insert the current date ( or timestamp ) into your cell A1 whenever something is typed in cell B1, just perform the following steps:

  1. Select your cell A1;
  2. in the Formula Bar, enter the formula =IF(B1<>"";NOW();"");
  3. Right-click inside your cell B1, and choose the menuitem “Format Cells...”;
  4. In the dialog that appears, select the tab “Numbers”;
  5. In the listbox “Category”, select the item “Date”;
  6. In the listbox “Format”, scroll down to select your preferred Date/Time format.
  7. Click OK.

Hope it helps, lib

edit flag offensive delete link more


But with this method the timestamp always shows the current date/time when the sheet is recalculated, not the date it was first inserted.

MrP gravatar imageMrP ( 2019-01-22 02:02:09 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-04-11 12:29:54 +0200

Seen: 1,065 times

Last updated: Feb 05 '18