Automatic Time Stamp in Excel

So the following is a question I had from one of my students:

Kelly, can you help me modify the formula on the “Delivery Time” column? Instead of having the date appear, I’m trying to have the time that I enter it in the spreadsheet?

Thanks

Tom
Hi Tom,

Sorry about the delay in getting back to you! Did you mean like a time stamp? When did you want that to happen? For example, when you enter X date, the time stamp would show up.

Just clarify and I'll look into it for you.

Thanks, Kelly

Yes something like that. I want it to show up with a time instead of a date like the formula is right now.

Hi Tom,

I THINK I have it!

Before you open the spreadsheet, you need to set up Excel to run properly for it. You have to turn on the iterations.

File > Options > Formulas

I'm including the article that goes with how to do this. But don't worry to much with understanding it. I think the formula is working the way you want it.

Thanks!Kelly

Here’s the bottom line:

Tom wanted the current time to appear automatically in the Delivery Time column when he entered a date in the Delivery Date column. Basically a time stamp.

I found the answer to this on one of my favorite websites, Chandoo.org. It’s a fabulous Excel website!

This is the link to the article I used, AutomaticallyInsert Timestamps In Excel Sheet Using Formulas.

First you have to set up Excel to enable circular cell references.

“Now, by default iterations in formula computation is turned off. That means when you write a circular formula or create a circular reference, excel shows you a message warning you and it doesn’t evaluate the formula. To turn the iterative calculation, and thus enable usage of circular formulas, go to menu > tools > options > calculation tab and check the iteration box. In excel 2007, you can go to office button > excel options > formulas > iteration area.”

In Excel 2013-2016 it’s File > Options > Formulas> in the Calculation Options section.

 

Now in the Delivery Time column, you would put the following formula:

=IF(Q2<>"",IF(R2="",NOW(),R2),"")

“What it does?

It checks cell C3 (Q2) and whenever it is not empty it runs the circular formula IF(B3 (R2)="",NOW(),B3 (R2)) which fetches NOW() value only if the cell B3 (R2) doesn’t already have a value, thus serving timestamps.

The above formula works only if you have enabled iterative calculation mode as described above.”

Even if you don’t completely understand the concept, trust that it works!

Later I’ll post how we calculated the Transit Time and On Time columns.

Downloads

Time Stamp with circular formulas.doc

TimeStamp sheet.xlsx

An Introduction!

Let's answer the obvious first question...what the heck does PCF1 mean?? Answer to follow in the next post!

I'm starting this blog in an attempt to help people with their Microsoft issues. California Computer Schools offers "tech support" to all of our students. This means after attending a class with us, students can contact us with any questions they might have with regards to the class. Now I bend the rules on a regular basis and pretty much try to answer any questions that come my way. I've decided to share some of those questions with anyone who might be interested or maybe they have had the same issues in the past.
My plan is to update this blog weekly using questions I have received in the past. Maintaining my student/clients privacy is a priority to me, so I will remove any personal information associated with the question. Not only will I provide the question and answer, but I will also provide the research I did to resolve the problem. Going forward with new questions, I will make sure to site the sources that I used, however I may not be able to site all the sources for some of my older questions. My apologies in advance to any resource I failed to site. Please contact me and I will update my post. 
Please feel free to ask any questions you might have and I'll give it my best effort to assist you!