Do you need to insert a timestamp in Excel? This question is one of THE most requests I receive on a regular basis. Although there is no built-in function or process in Excel to insert a timestamp, it is possible to create a timestamp in Excel with formulas and a little tweek to iteration settings in Excel. But more about that later. This article will walk you through the formula to create a timestamp in your Excel worksheets along with a simple-to-follow example. Lets get started!.
When working in Excel under a lot of circumstances, entering dates in Excel is a straightforward process. For example, dates are entered for invoices or a project plan, this would normally be carried out manually. This is very slow and manual, There is a much more efficient way to achieve an excel timestamp.
Ways To Enter Date And Times Into Excel.
There a a few ways to enter dates and times into Excel. I differentiate these from the automatic timestamps. But, to understand the differences in these methods is important. So here are the two ways users normally attempt to enter date and time into Excel. Both of these functions are known as volatile functions. These types of function recalculate every time there is an update in the Excel workbook. This is an important point in this solution.
The NOW Function To Enter Date And Time
The NOW() function in Excel will enter the current date and time from your system clock. The syntax is easy. Type =NOW() and hit enter. It is important to note that Excel stores date and times as numbers, so ensure that your cell formatting is correct to display date and time.
- Right click on your cells
- Select Format cells
- In the dialogue box select “dd-mm-yyyy hh:mm:ss”
- Hit Ok.
As already stated above this function is known as a volatile function. This means that Excel recalculates this function every time there is a change in the workbook. So, just because your enter the NOW() function at the current date and time, it WILL change when the workbook updates. So, as you can see this is probably not the solution to timestamp your Excel workbook.
The TODAY Function To Enter Date Into Excel.
The TODAY() function in Excel will enter the current date inot your Excel worksheet from your system clock. The syntax is easy. Type =TODAY() and hit enter. It is important to note that Excel stores date and times as numbers, so ensure that your cell formatting is correct to display date.
- Right click on your cells
- Select Format cells
- In the dialogue box select “dd-mm-yyyy”
- Hit Ok.
Again, this is a volatile function recalculating everytime Excel updates. This function in will also not give the desired Excel timestamp in Excel.
Using Excel Shortcuts To Insert Date And Time
Ok, so we cannot really use and Excel function currently to create a timestamp in Excel. We can however use a very manual process. Thee are tow shortcuts that insert the current date and the current time. These two shortucts do create a timestamp which is not volatile. however, the process is very manually and the user physically has to insert these shortcuts rather than the timedtamo happening automatically.
here are the shortcuts to try.
- CTRL+ ; to enter the current date or
- CTRL+SHIFT+: to enter the current time
The Solution.
Now, the solution most of my questions refer to is that of entering data into one cell in excel, and a timestamp appearing in another. For example a login sheet, or date entry sheet, login the date and time users entered data. So, there is a way to do this, using formulas but with a little Excel trickery or magic and an understanding of Circular References. Let’s get started.
Circular References Explained.
You may have come across them before, but if not, here’s a quick rundown circular references. A circular reference refers back to itself either directly or indirectly. Here is an example.
If I type =A4+1 in cell A4, I directly refer to the cell containing the formula. The consequence would be a warning from Excel if you did not create this circular reference intentionally. This example of an is intentional, and the result of the formula is zero.
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. So the result is a regular formula.
If I go back to cell A1 and enter the formula =A2+3, this will create a circular formula. I base the formula in A2 on A1, and I am writing the formula in A1. Essentially, the formula is referring to itself. See how this works?.
You can see above that Excel indicates the circular reference with a blue vertical line after selecting OK to its warning. So, let’s take this a step further.
Excel Iteration Settings.
There is a setting in called ‘iterations’ in Excel. This setting controls how many times a formula recalculates. Without this setting, calculations would go into an infinite loop and eventually stall. By default, this setting is switched off in Excel.
So, this is the reason when you type a circular reference, Excel throws you a warning. It stops and doesn’t calculate the formula as it could run infinitely. Simply follow the steps below to change the iteration settings to control how Excel formulas calculate. Take a look for yourself.
- Excel Options.
- Formulas.
- Calculation Pettlings
- Select enable iterative calculation
- Change the default setting to 1. Recalculation will happen once before it stops.
Consequently, in our case, this will result in cell reference A1= 5
So, to insert a timestamp in Excel, we can exploit this circular referencing and iteration to our full advantage. We base the timestamp process on the above theory of iteration.
Type the following formula into cell A1
=IF(B1<>””,IF(A1=””,NOW(),A1),””)
Then type something into B1. The data and time is stamped into the cell. Drag the formula in Column A down to allow time to stamp all the way down the column.
How The Excel Timestamp Works.
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. Excel inserts the timestamp.
So, why not go ahead and give this a go. Let me know if you have any other way of creating a timestamp in Excel?
Jurell says
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
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
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
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/
Tim says
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
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
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
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
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?
Fadly says
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
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
Kyle says
This was just what I was looking for! thanks for sharing!
Mike says
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
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
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
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
Thank you barbara!
=IF(B1″”,if(A1=””,NOW(),A1),”‘) this is working.
thanks a lot again
Sree
Barbara says
You are welcome- no problem
Barbara
Tom says
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
=IF(B1″”,IF(A1=””,NOW(),A1),””)
oh… these greater than less thans
works _for every cell except for A1.
That is great
Barbara says
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
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!!!
solublefish1 says
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
solublefish1 says
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
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
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
Nadine
Ensure your cell formatting is dd/mm/yy hh:mm- you should be good to go.
Regards
Barbara
Laura says
I am having the same problem and cell formatting is appropriate?
Barbara says
@Laura
is the iteration setting correct?
regards
Barbara
Jorge says
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
Aaditya says
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
Molly says
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
Molly
Glad to help..
Regards
Barbara
Kim says
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
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
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! 🙂
OLLIE says
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
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
Dolse says
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
Doise, have you an example work sheet?, you can mail me at barbara [at] howtoexcelatexcel[dot] com
Regards Barbara
Koldfront PAC1401W says
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?
shy says
This was a super help! Exactly what I needed! Thanks a ton!!
Eddie says
Hi,
Is there a way this update can span multiple colums?
Barbara says
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
Beth says
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
Without having to manually protect the cell that is
Bill Garnett says
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
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
R says
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
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
khromwell Badon says
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
Andrew E says
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
Andrew
Did you follow all of the stages in the blog post, ie the iteration settings?
Regards
Barbara
Andrew E says
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
Lars says
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
Lars
Thanks for the feedback and the information about LibreOffice- good to know
Regards
Barbara
Raj Kumar Kothari says
Great Trick !! Truly Awesome !
JC says
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!
dee says
This is great but how do i get it to work in a data table
Linda Nelson says
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
Rob says
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
Rob says
Apologies – ignore my last comment – it was because I wasn’t using A1, I was using A2. Duh!
Barbara says
All Good Rob…All Good
Mhace says
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
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
Michelle says
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
Michelle
Can you supply a few more details of what you are trying to acheive
Barbara
Nick says
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
@Nik
Check iteration settings and formatting first of all.
make sure all are exactly in tutorial
Barbara
Annie says
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
Annie
Make sure you re type the formula, the copy and paste does not work when copying to excel form the blog.
Barbara
Aminu says
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
Anna says
Is there a way to adjust this formula so that it adds a timestamp when a checkbox is checked?
Anna says
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?
depaul says
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.
Lee says
Barbara,
How do you get the time stamp to not update when you save the workbook?
Tara says
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.
rob says
Rewote formula =IF(B10″”,TODAY(),””) Much cleaner and less errors
rob says
Rewrote formula =IF(B10″”,TODAY(),””) Cleaner and less errors – same results.
Alan Reynolds says
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
Alan
Try to format your target cell as date. It maybe general or text.
Hope that helps
Barbara
Alan Reynolds says
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
zeb says
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.
Andrew says
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 🙂
DonW says
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?
Anshul says
Gr8 Stuff Barbara!! Thanks for this wonderful formula 🙂
Kelly Samson says
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
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.
BUDI says
thanks a lot… very very much
Barbara says
Glad it has helped you
Regards
Barbara
Matt Horsley says
Great! Been looking for this for a long time. Thank you for posting it (years ago)!
Jon says
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.
Any thoughts as to what might be causing this? I am using excel 2010
Thanks
Bgalindo says
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?
suraj says
I have typed your formula but its showing 0:00:00 in the cell…
Barbara says
Hi Suraj
Please ensure you have correct formatting of the cell, and dont copy the formula with copy and paste as on some browsers it does not come across all intect.
or, send snapshot of your formula?
thanks
Barbara
annette says
Was checking out the time stamping info. All worked eventually but my time and date is 1900-01-00 00:00!!!! How to overcome that?
Barbara says
Hi Annette
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
Frank Johnson says
Thanks Barbara – this is very helpful! I do have a couple of questions:
1. I assume setting the maximum iterations to 1 will only affect the workbook in which I change the settings.
2. If I want the maximum iterations to be 1 but only for one particular worksheet (tab) in the workbook, is that possible?
Thanks in advance for any help you can offer – I really appreciate it.
Frank
Barbara says
Frank thanks for the comment. As far as I am aware the iteration setting is at a workbook level. If this is an issue then you may like to use a small peice of VBA code to get the Timestamp. You can see the Excle tip right here.
http://www.howtoexcelatexcel.com/vba/create-a-date-time-stamp-in-excel-with-vba/
Let me know if this helps
Regards
Barbara
Fred Chaytors says
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
Barbara says
Fred
Glad it has helped
Regards
Barbara
yasira says
I’m unable to do the formula after following all the steps
can you please help me?
Barbara says
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
lasertest says
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!!
Bilal says
Hi
In my case it is not working it just shows this kind of number: 49873.123
How can I fix this?
Barbara says
Hello Bilal
Have you checked the cell formatting?. Change it to something like this depending on the level of detail of date and time you want.
dd/mm/yyyy hh:mm
Brian G says
This is excellent. Thank you for the post. Works perfectly. I’ve been wanting to do this for some time.
I do with the time stamp would update as you add new information however, I can easily clear and then add to the cell to update the time stamp.
Ricardo Chacón Guzmán says
Excelent Barbara, thanks for sharing!!!
Barbara says
Glad You Liked It
Barbara
Peter says
hi,
so after copypasting the formula its shows only 00:00:00
=IF(B1″”;IF(A1=””;NOW();A1);””)
Barbara says
Peter
change the cell formatting, Home Tab – Number – select the type you want. You may need to select a custom formatting for date and time
Alastair says
Hi, May i know if this is an array formula? This is because i would like to bring the excel spreadsheet into the mobile application of excel. However, the mobile application does not allow array formulas and will set it to “Read-only” mode. If there is any other alternative to transferring the spreadsheet to a mobile application, please teach me!
=IF(B2″”,IF(C2=””,NOW(),C2),””)
gmoda says
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.
Terrific blog!
Cẩm nang in ấn says
Very descriptive blog, I enjoyed that bit. Will there be a part 2?
de-l-esthetique.tumblr.com says
Yоu are a vᥱrʏ smaгt individᥙal!
ugoda jak napisać says
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
msp airport taxi says
This website truly has all of the information and facts I
wanted about this subject and didn’t know who to ask.
Coupons says
Hi to all, the contents existing at this website are really amazing for people
experience, well, keep up the good work fellows.
Foreclosures says
That is very attention-grabbing, You’re a very professional blogger.
I’ve joined your feed and sit up for in the hunt for extra of your
fantastic post. Also, I’ve shared your web site in my social networks
Real Estate gurus says
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.
Priya says
Hi Barbara,
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?
Thanks,
Priya
Barbara says
Priya
Hi what happens when you click in the formula?
Barbara
https://youtu.be/KesAcyQEu9U says
WOW just what I was searching for. Came here by searching for Excel Tips
Colorado realty says
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!
Barbara says
There is a unsubscribe area at the very bottom of the email.
Regards
Barbara
CiE says
this formula is great! i have a question though. is it necessary to always check the Iteration to be in default value of 1 every time the file is accessed?
Barbara says
Hi Cierlo
Yes, if this is not the best solution for you then try the Macro method. This may be more suited.
http://www.howtoexcelatexcel.com/vba/create-a-date-time-stamp-in-excel-with-vba/
Hope this helps
Barbara
Julio Gudiel says
Barbara,
I hope this thread isn’t dead
I have the same issue with the date showing 00/01/1900
And i have tried so hard unsuccessfully to fix it, I would greatly appreciate it if you could help me figure it out.
Here’s the link to my worksheet https://drive.google.com/open?id=0B96RActV9Or7N0kwZmxkQTVHd2c
Thank you in advance,
Julio
Barbara says
Hi Julio
This worked good for me. Enable the iterative calculation setting. Format the date.
Regards
Barbara
mistiendasvirtuales.es says
Great article.
Barbara says
Thanks!
Abdiel Ruiz says
Just here to say Barbara you are AWESOME…. hope I could buy you a coffee or something 🙂
James Adams says
Barbara,
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?