# Static Date and Time Stamp Formula



## krisso

Afternoon all,

I asked this question a while ago on this forum and have searched my posts but I cant find the answer anymore. We use a spreadsheet online for daily funds in and out and I want to date stamp the entry when people add new information to it. I have setup a table so the formula will copy down when new lines are added. I thought the formula was along this line =IF(A6<>"",IF(J6="",NOW(),J6),"") but I cant get it to work in the online version of excel and when i test it on excel each time i make a change to the spreadsheet it is updating all lines to the same date and time. Can anyone help?

Thanks
Chris


----------



## AliGW

Was this the thread in question?

https://www.mrexcel.com/forum/excel-questions/406970-convert-date-text.html


----------



## krisso

No it wasn't that post. 

it was one similar to the formula I posted above and I am sure I labeled it static date formula but I cant find it. Im sure the above formula is wrong as well as I want it to post a date and time in column J when A is populated but I keep getting a 01/01/1900 00:00 stamp on each line and that is all that updates when I add lines


----------



## AliGW

Maybe it was a different forum? 

Your formula is working perfectly here. However, every instance of it will update each time the workbook is recalculated, that is every time you hit the ENTER key. The only way to fix the date stamp is to copy it and paste it as a value as soon as you have entered it to overwrite the formula, or to use a bit of VBA to fix it.

This might help: https://www.exceltip.com/vba/insert-date-time-stamp-with-vba.html


----------



## krisso

I have tried to redo it from memory. It was definitely static upon line entry. VBA doesn't work with the browser version of Excel sadly


----------



## AliGW

It can't have been static if it was derived from a formula without some macro to fix it, and as you say, they don't work online. Sorry, can't help any further, I'm afraid. Maybe someone else will be able to.


----------



## krisso

Thanks for your help  I promise it was a formula that was circular referenced that made it static 

Wish I could fine the original post as I only use this forum for excel


----------



## AliGW

There's only one other thread by you that relates to dates and times, and that's one asking about increments. There are no others apart from the old one I linked to above. Perplexed!


----------



## krisso

As am I as it means the thread has been moved or deleted


----------



## wideboydixon

You'll need to enable iterative formulas. More information is here:

How to insert timestamps in excel sheet using formulas | Chandoo.org - Learn Microsoft Excel Online

Not sure if that will work online though ...

WBD


----------



## krisso

Afternoon all,

I asked this question a while ago on this forum and have searched my posts but I cant find the answer anymore. We use a spreadsheet online for daily funds in and out and I want to date stamp the entry when people add new information to it. I have setup a table so the formula will copy down when new lines are added. I thought the formula was along this line =IF(A6<>"",IF(J6="",NOW(),J6),"") but I cant get it to work in the online version of excel and when i test it on excel each time i make a change to the spreadsheet it is updating all lines to the same date and time. Can anyone help?

Thanks
Chris


----------



## ClwnMan76

wideboydixon said:


> You'll need to enable iterative formulas. More information is here:
> 
> How to insert timestamps in excel sheet using formulas | Chandoo.org - Learn Microsoft Excel Online
> 
> Not sure if that will work online though ...
> 
> WBD


I know this i s old, but in my search of the internet this was the first thing I found that described what I wanted and it worked perfectly! So glad you keep this forums for so long!! Thanks!! 🙏


----------

