date and time needs to be auto populate when i insert data in another column.

Hi Team,

I have the function below in A1 column, below function I was tried to use because when I enter data in B column i need the data entered date and time in B column, however I have entered data in B1 and B2 column around 10 am and I got the exact date/time populated in A1 & A2 column, I am happy with that but after 2 hours I entered data in B3 and B4 column but the issue is data & time has been changed to current date from A1 to A4 column. Please advise and assist us on getting new function to auto populate the data and time in A column. No need macrosssssssssssss.

=if(COUNTA(b1)>0,Now(),"")


Regards
Girish

Not sure I understand what you want Can you upload a sample workbook showing what you have and what you want please

You can create a UDF (User Defined Function) to achieve this. So instead of using NOW(), you will need to use the function defined by you to get the static date and time. Are you comfortable with UDF?

OK follow these steps...

- Open VBA Editor by pressing Alt + F11 --> Insert --> Module --> Then in the opened code window, paste the code given below --> Close VBA Editor --> Save your worbook as Excel Macro-Enabled Workbook --> That's it.

Now in your sheet you can use UDF as follows

in A2

Please Login or Register  to view this content.

and custom format the date column as dd/mm/yyyy h:mm or format of your choice.

UDF

Please Login or Register  to view this content.

Does this help?

Double post :-)

@Crooza
UDF will be recalculated in A2 if B2 is changed or A2 is edited and this even will not affect the other cells in col. A having this UDF. That's what I think. In fact I have never used it before so not sure about the every aspect of it.

Originally Posted by Giri.hb

Please try to get some function instead of macro please...

Goto this site:

http://www.mcgimpsey.com/excel/timestamp.html

You want to use the method described as: Using circular references and worksheet functions.