Create A Timestamp In Excel With Formulas

Under lots of circumstances, would you enter the date on a spreadsheet for an entry onto an invoice, a project plan etc and you would do this by entering the current date and time. This however is usually done manually by the user, and takes way too much time for us wanting to Excel At Excel.

We probably all know that we can use the =TODAY() formula or the =NOW() formula to enter a date into Excel, which update automatically when – this is known as “volatile” as the function recalculates when Excel updates.

This is not really any good for a timestamp, as we would want an invoice entry or project update to be static. We therefore can use

CTRL+ ; to enter the current date orCTRL+SHIFT+: to insert the current time

These both will remain static, but are still very manual having to insert the date and/or time with keyboard the above shortcuts. That’ all well and good but wouldn’t it be great to just insert your changes to a worksheet and the timestamp appear as you type?. You can but it takes a bit of Excel magic and an understanding of Circular Formulas.

What’s this about Circular Formulas?

You may have come across them before but if not here’s a quick run down.

A circular formula is one that refers back to itself either directly or indirectly. Let’s take a look at an exmaple.

Lets look at a direct example.

If I type =A4+1 in cell A4 I am directly referring to the cell I am writing the formula in. This results a warning from Excel in case you did not create this circular reference intentionally (as we did) and the result of the formula is zero.

Let’s look at an indirect example

If I type 2 into cell A1,

then type =A1 into cell A2

then A2 refers back to A1 and gives a result of 2 which is a normal formula.

If I then go back to cell A1 and enter the formula =A2+3, this will create a circular formula as A2 is based on A1 and I am writing the formula in A1 so essentially the formula is referring to itself.

You can see above that the circular reference is indicated by Excel with blue vertical line after selecting OK to it’s warning.

Ok so let’s take this one step further…..

There is a setting in Excel called ‘iterations’, which tells it how many times to recalculate a formula. Without this function, Excel would go into an infinite loop and eventually stall. By default this is turned off in Excel which is when when you type a circular reference , Excel throws you a warning and doesn’t calculate the formula as it could run infinitely.

If we turn on the iterations function

1. Office button

2. Excel Options

3. Formulas

4. Iteration

5. Change the default iteration to 1, so it recalculates one time before it stops.

In our case this will result in cell reference A1= 5

So, at the start of this post we were to insert time stamps into Excel, we can exploit this circular referencing and iteration to our full advantage based on the theory above to insert our required time stamps.

Let’s go ahead and do that- in a scenario that we want the timestamp to be in Column A when we are entering data into Column B

1. Type the following formula into A1

=IF(B1<>””,IF(A1=””,NOW(),A1),””)

2. Type something into Column B

3. Your date and time will be stamped.

4. Drag the formula in Column A down to allow time stamping all down your column.

Isn’t that just the bees knees?

An explanation of what the formula is doing.

The formula is checking cell B1, and if it is not empty it runs the circular formula =IF(A1=””,NOW(),A1) which returns the NOW() function if A1 does not already have a value- thus stamping the time.

Go ahead and give this a go, let me know if you have any other way of creating a time stamp in Excel?

Want To Watch My Latest Video On How To Create A Timestamp In Excel With A Formula?

Comments

Hi,
This is awesome…thank you for the formula. I have a question. Does this update everytime the cells in column A changes? So for example Cell A1 changed yesterday timestamped 11/07/2011. But I changed it again today. Does it time stamp 11/08/2011?

Great formula, I’ve used it for work already, and came through in a pinch in creating a spreadsheet for my team. Quick question though, my timestamp formula doesn’t generate the time as well, just the date. So it only spits out “12/21/2011” for instance, not “12/21/2011 11:56”. Any ideas how to fix that?

Also, what is your solution to fixing the fact that Excel resets the maximum iterations setting for each new session of Excel? Is there a way I can save that setting so that it is locked for a certain workbook?

The iteration settings will remain within the workbook you have set once you save your workbook. Excel opens with the default of iterative calculation (as unselected) setting for each new workbook opened.

Glad you liked the formula, it does come in handy. Also good point to note on the date and time.
If you format the timestamp column as dd/mm/yy hh:mm that you sort that for you. Use Format- Cells- Date- Custom as select the above formatting.

When you use the NOW function in that formula it appears that it is also attaching the time to the date. If you then use that data for a pivot table with column headers to be the date you exceed the number of days as it makes an entry for every time stamp instead of every day. Is there an easy fix for this?

I already used your formula and it was great.. But I came up with a problem when I want to share the excel spreadsheet over google docs.. google docs doesn’t support iteration. Can you suggest another way to solve this. TIA for your reply.

there was a small error in formula after last edit on the post it was missing the <> at the start of the formula- appologies. To get date stamp not just time, format your column A with format dd/mm/yyy or whatever you want to. Let me know how you get on

I believe this is a formating issue w/ the quotations in the formula when copying from this site to Excel. I noticed after clicking OK to the error prompt Excel highlighted the first of quotations (indicating/debugging where the first problem resides). I retyped the quotations, received the same error. Again, after clicking OK to the error prompt Excel highlighted the second set of quotations, I replaced those, as well as the third set to resolve the error. Works fine now. Just to be safe you may want to manually enter the entire formula instead of copying and pasting to avoid any errors such as this one. Hope this helps!!!

Actually looks like I figured it out. The formula doesn’t work if you have a list validation set up in A1.
If I type something into A1 I get the expected datestamp. If I enable validation and create a dropdown list, the formula returns January 00, 1900. Any idea how to fix this?

I am having the same problem concerning a list validation and 01/00/00 diplaying as the date. What was the solution? Also, will the date change everytime I open the workbook to the current date or will it remain the date the cell was last updated?

I am a high school librarian and i am wanting to create a student sign in sheet for the library. I don’t know much about excel. I’ve read all the posts and I’m still lost. I’ve set the iteration and tried the formula. I’m not getting a date/time at all. I’m getting an odd string of numbers. Maybe i need to have something else set up as well. I would appreciate any help. What if i want the date/time stamp to be in column “D”?

Thanks for the email. I have entered the formula into your worksheet, just needed a tweak, to reference the fact that your entry for data is in column A. Also I reset the iteration value which was not saved. Just drag the formula down the column to create more timestamps

Hi, I have also the same problem, I am currently using excel 2010 and can’t keep a formula. I am monitoring students attendance sign in but I couldn’t make a formula at all. I hope you can help me. thanks! 🙂

I’ve been looking for a time stamp formula to use with a macro button. In other words, every time the button is selected it records the current time in the next row in a column. Any suggestions would be greatly appreciated.

I wish to have the know-how to upload hyperlinks plus video clips of my vacations in addition
to the places whereby I’ve run a race. Ideally, I would personally choose to have the ability to update my blog with entries from my iPad. What might you suggest is most likely the right way to go about executing this?

I’m trying to set up a formula to automatically enter the current date in a cell once another cell is updated. I don’t want this date to every change after the entry is made. so we can track information by date it was entered. The formulas above are just not working. Can any help me.

I don’t know if its possible to create a timestamp from a text. I am currently working on a spreadsheet to consolidate cases or data at work. The idea is if A1 has a value “CLOSED” B1 should have a timestamp or date when a case or request has been closed this is to have a automatic notification with timestamp.

I have entered the formula above and works fine except for one thing. Each time I open the Excel document the time stamp changes to that specific time in which I have just opened it. I want to keep track of when info is entered in a specific cell. For example I am keeping track of time that I have worked on a project so when I put an X in a cell that says I worked on it on Monday at this time. Each time I open the document the time is changing on me. any way to make it so it doesn’t change the time?

Dear Barbara
Thanks a lot, nice and convenient. I would call myself experienced user, but I could’nt figure out to create this timestamp. I use it to timestamp some weighing measures, which need to be analysed together whith other data from a datalogger. And just to inform you, it works fine in libre office in ubuntu.
Regards
Lars

I used your formula and it worked perfectly. But I have a very small problem. I created a worksheet that has two kinds of time stamps. One is start time. So, the START TIME is linked to a drop down menu that i created on excel so, it automatically registers the time every time I select something on the menu. Next one is the END TIME which is linked to the same drop down menu but different line number. (To explain it, the START TIME is linked to F5 while the END TIME is linked to F6.) It both works well. Now, problem is… I have to create “hours” where it shows the interval between the START and END time in a circular command. Please help me on this one as I have been working on this for 4 days already.

Hello,
I can get the formula to “work” but I have tried “several” different options for formatting the cells and it just keeps coming up #VALUE!?????? I used “custom” formatting: mm/dd/yy hh:mm:ss AM/PM the last time but I still get #VALUE!
Also, I noticed in the former comments that the Iteration does not work in Google doc’s – is this still true?
Thank you

Hello,
Thanks for this formula! However, I will sometimes have black fields in some columns depending on the week. Is it possible to use this same formula using a range? I tried and the formula didn’t work. Any ideas?

Hi Barbara,
I’m copying your formula across, and adjusting the relevant cell references. The error message is then duely coming up, but it wont give me the option to continue. If I click OK it deletes the formula. What am I doing wrong?!
Many thanks,
Annie

I came across your formula and its genius! It works! However, the file I am working on is shared across a team and though the iteration setting shows up on my version as 1, any other person accessing the file gets the circular reference error thrown at them. How to I get this sorted please? I have un-shared the file, saved it again and shared it hoping the setting would apply across the other members systems but that didn’t work.

Actually, I also have a second related question. If I had multiple checklists that each created a static timestamp for each checkbox that was checked off the list, would it be possible to collate those timestamps on a separate spreadsheet so that I could track the progress of all the checklists in one spot?

I used your formula and it worked well for a time. Each time the file would open I would get the circular ref alert. After a time, now the time stamp does not work and I do not get the circular ref alert. I use a validation list in my cell that activates the time stamp. I have dragged the time stamp formula down multiple cells, I have copied and pasted the formula in multiple cells, I have even keyed in the formula in multiple cells and the time stamp will not work. What gives?
I once was stamped but now I’m empty
My cell no longer strong but wimpy
Circular ref is on the side line
This error now kicks my be hind.

How would you go about locking the timestamp created once someone enters a value in the cell next to it? For example, if you delete the data, the timestamp also deletes. I want the timestamp to remain regardless if someone deletes something in the cell already entered.

Many thanks for your email and your suggestion. I had tried formatting my target cell as date but Excel rejected that because I had text as well as an & and the today function in the one cell. Excel kept changing the format back to general.

Today after more googling I learned how to add a text box to a chart in Microsoft Excel for Mac 2011, and with some effort I was able to fill that text box with the contents of TODAY() in a date-formatted cell on a worksheet, then move the text box just behind the chart title so that it looked as if it were one field.

But that seemed very klutzy and fortunately I found a better way – it turns out that I needed to concatenate my text in one cell with today’s date in a different cell while converting the date to text.
For example if the desired chart title’s text is in R3 and today’s date is in S2, then the concatenated cell to use as the chart title would be set to:

=R3&TEXT(S2,” d mmmm, yyyy”)

Sounds simple once you know it, but nowhere findable in the Microsoft documentation 🙂

I hope this is still an active thread, as I have found it to be very useful so far. I do have a question about how to modify this timestamp formula for my specific needs. I have a whole row of data and would like the time stamp to apply to about 25 different cells in that row. I tried something like this and got an error:

=IF(A4:M4″”,IF(N4=””,NOW(),N4),””)

The original formula works great if it is only pointed at one specific cell. When I try to point it at a range, it doesn’t work. Thanks in advance for your help.

Hello and thanks for posting this. I’ve tried to use a variation of this formula for an excel sheet and whenever I enter information into it, all the timestamps will change at once. I’ve tried the iterations and even setting the spreadsheet to manual calculations, but when the calculations are run, the timestamps are reset.

Hello I recently found this incredibly helpful to do my offices item tracking. But recently we’ve decided to go to an Ipad and I was wondering if there is fomula similer to this that works on that platform since this one doesnt?

Hi, very many thanks for the date stamping method. I am currently cataloguing a collection of books and valuing them at the same time. I have been using key strokes for the date and some times pressing wrong keys. for me a great step forward.
Cheers

Of course I can. I have recently added in a download example workbook. Feel free to use. Just check it out at the top of this blog post. I should imagine that will help. If not let me know.
Thanks and regards
Barbara

An intriguing discussion is worth comment. There’s no doubt that
that you need to write more about this topic, it may not be a taboo
subject but typically folks don’t speak about these
subjects. To the next! Cheers!!

Have you ever thought about adding a little bit more than just your articles?
I mean, what you say is fundamental and everything.
However just imagine if you added some great graphics or videos to give your posts more, “pop”!

Your content is excellent but with images and clips, this site could definitely be one of the very best in its niche.

I do not know if it’s just me or if everybody else encountering
issues with your website. It appears like some of the written text on your
posts are running off the screen. Can someone else please provide feedback and let me
know if this is happening to them as well? This may be a problem with my web
browser because I’ve had this happen previously. Kudos

With havin so much content do you ever run into any issues of plagorism or copyright violation? My website
has a lot of unique content I’ve either created myself or outsourced but it
looks like a lot of it is popping it up all over the web without my agreement.

Do you know any solutions to help stop content from being stolen? I’d certainly appreciate it.

It doesn’t work on my sheets. I downloaded your sample sheet and that too throws back the same result as soon as i click into the formula cell (it is fine as long as I don’t do that). Can you please help?

After I initially left a comment I seem to have clicked the -Notify me when new comments are added- checkbox
and now each time a comment is added I receive 4 emails with the exact same comment.
Is there a way you can remove me from that service?
Appreciate it!

I have created a daily checklist as a management/audit tool for my global team. I am using it as a way to track whether my employees in India are completing their daily responsibilities by a given time. I use a new version of the same file every day, and am hoping to archive the prior results for sudit purposes. For this reason, I have the file as a shared file, but the problem I am running into is that I can not get accurate timestamps as the file only updates the time of my most recent save, not when each individual has completed their task at the specific time. Is there a formula for timestamps I can use where the timestamp value is fixed to the same time no matter which user has the workbook open?

[…] I used this trick on a workbook earlier this week. It will put in a date and timestamp based on when a value is entered into an adjacent cell. It may be worth looking at: Create A Timestamp In Excel With Formulas […]

[…] you can use a timestamp heres a link to a couple of tutorials on how to do that Create A Timestamp In Excel With Formulas How to insert timestamps in excel sheet using formulas | Chandoo.org – Learn Microsoft Excel […]

[…] may work for you its setting a timestamp you could combine with the IF conditions you want to set Create A Timestamp In Excel With Formulas How to insert timestamps in excel sheet using formulas | Chandoo.org – Learn Microsoft Excel […]

Your email address will not be published. Required fields are marked *

Comment

Name *

Email *

Website

Follow How To Excel At Excel

Need Answers To Excel Questions Like These?

*How Do I Create A Timestamp In Excel?
*I Want To Change The Width Of The bars On My Excel Chart
*How Can I Find Out The Length Of My Text in Excel?
Click the link below to receive more Excel tips' and my Free Ebook

Sign Up For My FREE Excel Tips Newsletter and receive your own E-book of my Top 50 Excel Tips.