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