• Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • More
    • Blog
    • Download Area
    • Excel Video Tutorials
Home » Create A Timestamp In Excel With Formulas

Create A Timestamp In Excel With Formulas

June 8, 2011 by Barbara

 

You can download the example workbook for this tip here [ddownload id="8526"]

What’s A Timestamp?

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 updates 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 or
CTRL+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 the 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 rundown.

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

Let’s 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 in 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.

Pin it! Share on Facebook

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 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.

Pin it! Share on Facebook

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 timestamps.

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 to stamp all down your column.

Isn’t that just the bee’s knees?

Pin it! Share on Facebook

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?

Other Formula Information You Might Like.

1. Hide Your Formulas From Prying eyes.

2. Debug And Understand Your Formula Errors.

[amazonjs asin=”1118902688″ locale=”US” tmpl=”Small” title=”101 Ready-to-Use Excel Formulas (Mr. Spreadsheet’s Bookshelf)”]

[amazonjs asin=”1118490444″ locale=”US” tmpl=”Small” title=”Excel 2013 Formulas”]

[amazonjs asin=”0789748673″ locale=”US” tmpl=”Small” title=”Excel 2013 Formulas and Functions (MrExcel Library)”]

 



Filed Under: Excel Tips& Tricks

Comments

  1. Jurell says

    November 8, 2011 at 7:39 pm

    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?

    Thanks,
    jurell

    • Barbara says

      November 9, 2011 at 2:06 pm

      Jurell

      No this formulas does not, it timestamps the time the cell was changed, and does not change if you overtype.

      If you delete the original value from the entry cell and then type a new entry — a refreshed timestamp will appear.

      Regards
      Barbara

      • Steve says

        June 14, 2015 at 4:50 am

        Can it be modified so that it does change/update when over typing?

        Also how can I modify the code to look at a range of cells to be changed? =IF(O31:O33″”,IF(P31=””,NOW(),P31),””) did not work correctly.

        Thanks.

        • Barbara says

          June 22, 2015 at 5:04 pm

          Steve
          Try this blog post here for alternative solution using tiny amount of VBA
          http://www.howtoexcelatexcel.com/vba/create-a-date-time-stamp-in-excel-with-vba/

  2. Tim says

    December 21, 2011 at 5:57 pm

    Hello,

    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?

    Thanks again!
    Tim

    • Tim says

      December 21, 2011 at 7:36 pm

      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?

      Thank you,
      Tim

      • Barbara says

        December 21, 2011 at 9:17 pm

        Tim

        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.

        Regards
        Barbara

    • Barbara says

      December 21, 2011 at 8:55 pm

      Tim

      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.

      Regards
      Barbara

    • Rusty says

      November 14, 2012 at 5:03 pm

      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?

  3. Fadly says

    January 12, 2012 at 2:48 am

    Hi,

    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.

    • Barbara says

      January 13, 2012 at 11:04 am

      Hi Fadly

      Indeed Google Does not support some functions and iteration is one of them.

      Any comments from any other Excellers greatly appreaciated on this one!

      Barbara

  4. Kyle says

    February 28, 2012 at 3:18 am

    This was just what I was looking for! thanks for sharing!

  5. Mike says

    May 4, 2012 at 2:45 pm

    This formula sounds like EXACTLY what I need, but I keep getting an error when I paste it into my spreadsheet.

    “The formula you typed contains an error”.

    Any ideas?

    • Barbara says

      May 4, 2012 at 6:15 pm

      Mike

      I have retyped the formula into the post, seems fine on Excel, want to try it?
      Let me know how you get on

      Barbara

      • Sree says

        May 8, 2012 at 12:00 pm

        Hi Barbara,

        this is not working for me . its throwing up error. It would be great if you can fix it.

        Thanks
        Sree

        • Barbara says

          May 8, 2012 at 1:26 pm

          Hey Sree

          I have re tried formula again and seems to work fine

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

          do you want to send me worksheet to barbara[@]howtoexcelatexcel[dot]com will take a look at it for you?

          Regards
          Barbara

          • Sree says

            May 8, 2012 at 6:06 pm

            Thank you barbara!

            =IF(B1″”,if(A1=””,NOW(),A1),”‘) this is working.

            thanks a lot again
            Sree

          • Barbara says

            May 8, 2012 at 8:06 pm

            You are welcome- no problem

            Barbara

          • Tom says

            May 15, 2012 at 4:53 pm

            Hi Barbara,

            This is going to be a great trick if I can get it working.

            I notice that your screenshot has:

            =IF(B1″”,IF(A1=””,NOW(),A1),””)

            But the formula you typed in red is

            =IF(B1<"",IF(A1="",NOW(),A1),"")
            _________________________________

            I tried both and I can't get this timestamp to work. When I type this
            =IF(B1<"",IF(A1="",NOW(),A1),"") the formula generates a BLANK cell.

            And when I type this:
            =IF(B1″”,IF(A1=””,NOW(),A1),””) it returns 12:00AM.

            It is as frustrating. Now I know why seniors never program their 12:00am blinking VCRs.

          • Tom says

            May 15, 2012 at 5:48 pm

            =IF(B1″”,IF(A1=””,NOW(),A1),””)

            oh… these greater than less thans

            works _for every cell except for A1.

            That is great

          • Barbara says

            May 15, 2012 at 8:12 pm

            Thomas

            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

            Regards
            Barbara

      • Bob says

        December 13, 2012 at 12:58 pm

        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!!!

  6. solublefish1 says

    May 18, 2012 at 5:21 pm

    I get a date result of January 00, 1900 using this formula instead of today’s actual date. Any idea of whats causing this?
    Thanks

  7. solublefish1 says

    May 18, 2012 at 5:36 pm

    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?

    • Barbara says

      May 20, 2012 at 5:50 pm

      Possibly your formatting of you cells, have you checked that- I also used a validation on my entry cells and works fine. Send on your worksheet – let’s have a look
      Regards
      Barbara

      • Nadine says

        April 18, 2013 at 1:12 pm

        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?

        • Barbara says

          April 23, 2013 at 8:18 pm

          Nadine

          Ensure your cell formatting is dd/mm/yy hh:mm- you should be good to go.
          Regards
          Barbara

          • Laura says

            June 27, 2013 at 8:57 pm

            I am having the same problem and cell formatting is appropriate?

          • Barbara says

            June 28, 2013 at 8:51 pm

            @Laura

            is the iteration setting correct?

            regards
            Barbara

  8. Jorge says

    May 23, 2012 at 7:34 pm

    Hello everyone,

    Formula works fine by adapting the original one to: =SE(b1″”;SE(a1=””;AGORA();a1);””).

    Thanks Barbara for your hints!

    Jorge
    Brasilia – DF
    Brazil

  9. Aaditya says

    July 2, 2012 at 9:05 am

    Hi

    I have a real time data which i need to save in a excel file with different enteries for different time slots.

    Can anybody help me how to do it at earliest

    thanx

  10. Molly says

    July 5, 2012 at 6:28 pm

    thank you so much for the timestamp! I’m a middle school teacher and this is how I am going to do the dreaded bathroom sign-out and after school sign-in sheets next school year

    • Barbara says

      July 5, 2012 at 9:21 pm

      Molly

      Glad to help..

      Regards
      Barbara

    • Kim says

      February 21, 2013 at 4:51 pm

      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”?

      • Barbara says

        February 26, 2013 at 9:32 pm

        Hello Kim

        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

        i hope this solves your problem

        • Toni Rose says

          August 14, 2015 at 1:37 am

          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! 🙂

  11. OLLIE says

    July 13, 2012 at 3:56 pm

    BARBARA,

    So what did you do in the instance of having a list validation follow the timestamp? Mine is popping out the Jan 1990 date and not the current one. Any way to fix this.

    Thanks,

    • Barbara says

      July 16, 2012 at 9:29 am

      Ollie

      I have data validation working no problem. Which cells have you validation on? I have validation on columns to the right of the cells with timestamp – validation being a yes or no option.

      Barbara

  12. Dolse says

    July 23, 2012 at 4:23 pm

    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.

    • Barbara says

      July 23, 2012 at 7:41 pm

      Doise, have you an example work sheet?, you can mail me at barbara [at] howtoexcelatexcel[dot] com
      Regards Barbara

  13. Koldfront PAC1401W says

    July 27, 2012 at 5:35 am

    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?

  14. shy says

    August 4, 2012 at 9:00 pm

    This was a super help! Exactly what I needed! Thanks a ton!!

  15. Eddie says

    August 27, 2012 at 10:11 pm

    Hi,

    Is there a way this update can span multiple colums?

    • Barbara says

      August 28, 2012 at 7:16 pm

      Hi Eddie

      Do you mean the time stamp update multiple columns? or a cell be timestamped when multiple columns are updated?
      Let us know
      Regards
      Barbara

  16. Beth says

    September 12, 2012 at 11:37 pm

    Barbara,
    Is there a way to automatically turn cell protection on once a value has been entered into cell B1? To prevent the time stamp from being manipulated at a later time?
    Thanks!

    • Beth says

      September 12, 2012 at 11:49 pm

      Without having to manually protect the cell that is

  17. Bill Garnett says

    September 16, 2012 at 3:57 am

    I thought this would be great, something I’ve wished for. Now I type @NOW() and then go back to the cell and copy then paste special as VALUES.

    However your formula does not work on my 1Mac using OX X Version 10-8-1

    Any fix?

    • Barbara says

      September 29, 2012 at 9:33 pm

      Bill
      Is it the @now() that does not work on your MAC or the the timestamp- both work on both my Windows and Mac OS?

      Regards
      Barbara

  18. R says

    October 12, 2012 at 12:44 pm

    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.

    • Barbara says

      October 12, 2012 at 1:41 pm

      Rs,

      couple of things
      1. did you copy the formuala, it needs to be typed out rather than copied
      2. did you amend the iteration settings?
      Regards
      Barbara

  19. khromwell Badon says

    October 27, 2012 at 4:26 pm

    Hello Barbara,

    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.

    Please help.

    Thank you,

    Khromwell

  20. Andrew E says

    November 14, 2012 at 6:48 pm

    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?

    Thank you and God Bless,
    Andrew E

    • Barbara says

      November 14, 2012 at 8:19 pm

      Andrew
      Did you follow all of the stages in the blog post, ie the iteration settings?

      Regards
      Barbara

      • Andrew E says

        November 14, 2012 at 8:39 pm

        Yes Ma’am I did. Not sure what I may or may not be doing correctly. I even did what you showed in your example and it changes both cells to the current time.?

        Thanks again for your help and any more information you may be able to supply.

        Sincerely,
        Andrew E

  21. Lars says

    December 27, 2012 at 8:31 pm

    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

    • Barbara says

      December 27, 2012 at 9:30 pm

      Lars

      Thanks for the feedback and the information about LibreOffice- good to know
      Regards
      Barbara

  22. Raj Kumar Kothari says

    February 8, 2013 at 4:44 pm

    Great Trick !! Truly Awesome !

  23. JC says

    February 21, 2013 at 6:11 pm

    Hi!

    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.

    Thank you!

  24. dee says

    March 11, 2013 at 3:31 pm

    This is great but how do i get it to work in a data table

  25. Linda Nelson says

    May 11, 2013 at 11:53 pm

    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

  26. Rob says

    May 21, 2013 at 4:28 pm

    This was just what I was looking for…

    But when I do it in Excel for Mac 2008, it’s just populating A1 with “Date-stamp” whether there’s anything in B1 or not.

    Allowing iterations or not doesn’t seem to make any difference.

    Can you advise? Thanks

  27. Rob says

    May 21, 2013 at 4:31 pm

    Apologies – ignore my last comment – it was because I wasn’t using A1, I was using A2. Duh!

    • Barbara says

      May 21, 2013 at 8:08 pm

      All Good Rob…All Good

  28. Mhace says

    May 27, 2013 at 8:51 am

    Hi Barbara,

    Nice formula, thanks for sharing. I used this in making our Timesheet for our team.

    But i just have a question.

    I’m having a problem in making a time difference using this timestamp.

    can you help me compute for the overtime and undertime using this timestamp?

    Thanks a lot. 🙂

    • Barbara says

      May 28, 2013 at 8:45 pm

      Hi

      No problem at all, send me a copy of your workbook, with what you require you can sanitise the data if want to.

      Regards

      Barbara

  29. Michelle says

    June 3, 2013 at 4:16 pm

    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?

    • Barbara says

      June 5, 2013 at 2:50 pm

      Michelle

      Can you supply a few more details of what you are trying to acheive

      Barbara

  30. Nick says

    June 25, 2013 at 1:34 pm

    Hi barbara,

    I used your formula in cell A1 but I’m getting weird values out as i type in B1 – B10 cells.

    Please help

    • Barbara says

      June 28, 2013 at 8:48 pm

      @Nik
      Check iteration settings and formatting first of all.
      make sure all are exactly in tutorial

      Barbara

  31. Annie says

    August 30, 2013 at 1:42 am

    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

    • Barbara says

      September 9, 2013 at 5:43 pm

      Annie
      Make sure you re type the formula, the copy and paste does not work when copying to excel form the blog.

      Barbara

  32. Aminu says

    September 11, 2013 at 1:33 pm

    Hi Barbara,

    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.

    Thanks

  33. Anna says

    September 12, 2013 at 5:20 pm

    Is there a way to adjust this formula so that it adds a timestamp when a checkbox is checked?

  34. Anna says

    September 12, 2013 at 5:44 pm

    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?

  35. depaul says

    September 13, 2013 at 2:10 pm

    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.

  36. Lee says

    November 11, 2013 at 6:54 pm

    Barbara,

    How do you get the time stamp to not update when you save the workbook?

  37. Tara says

    April 28, 2014 at 2:10 pm

    Hi,

    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.

    Please advise!

    Thanks.

  38. rob says

    May 21, 2014 at 5:44 am

    Rewote formula =IF(B10″”,TODAY(),””) Much cleaner and less errors

  39. rob says

    May 21, 2014 at 5:45 am

    Rewrote formula =IF(B10″”,TODAY(),””) Cleaner and less errors – same results.

  40. Alan Reynolds says

    May 27, 2014 at 10:23 am

    What a great trick! And what an interesting site. Thank you for both. I am glad I came across them, which I did when googling trying to learn how to add today’s date to an Excel chart.

    I know how to get most of what I want by making the chart title point to a cell set up like this:

    =”Sales this year to date as of “&TODAY()

    but the bad news is that what shows up is

    Sales this year to date as of 41786

    Any the instruction on how to do this (or pointing me to the right thread) will be appreciated 🙂

    • Barbara says

      May 28, 2014 at 8:29 pm

      Alan
      Try to format your target cell as date. It maybe general or text.
      Hope that helps
      Barbara

      • Alan Reynolds says

        May 29, 2014 at 10:12 am

        Good morning Barbara,

        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 🙂

        Thanks again. See many other neat things to learn on your site.

        Best regards,

        Alan

  41. zeb says

    June 23, 2014 at 9:06 pm

    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.

  42. Andrew says

    June 25, 2014 at 12:27 pm

    Hi Barbara,

    I’m getting the issue of the date returning as January 00, 1900. I used the formula from above as:
    =IF(C3″”,IF(B3=””,NOW(),B3,””)

    When I try to put this formula into my desired file I get the 1900 date, but when I put it into a new blank workbook it works completely fine.

    Not sure what the issue would be here, so any help is greatly appreciated.

    Thanks 🙂

  43. DonW says

    July 3, 2014 at 8:06 pm

    This is really cool. However, how can I do the same/similar where the “text” is in A1 and timestamp needs to be in A2?

  44. Anshul says

    July 15, 2014 at 2:16 pm

    Gr8 Stuff Barbara!! Thanks for this wonderful formula 🙂

  45. Kelly Samson says

    July 18, 2014 at 9:11 pm

    There is an easier way to write this code: The second IF check is not necessary by writing the ‘True’ condition to equal the blank answer.

    =IF(A1″”,NOW(),””)

    As listed above:

    =IF(B1"",IF(A1="",NOW(),A1),"")

    • Vkit says

      November 3, 2015 at 10:23 am

      guess the second IF is needed as Excel will recalculate the time as and when the worksheet is open or access again. Believe the second IF is some what to stop it to be recalculated and overwritten.