Hi Gang
I'm trying to do my spread sheet by entering in Column A Row 1 a number and having Column B Row 1 auto input the Date & Time. I'm using the Formula =IF(A1<>"",NOW(),"") in Column B Row 1. Problem I'm having is when I do Col A Row 2 the date and time changes the time in Column B Row 1 also. I don't know how to stop this
Can you help me adjust my formula.
Thanks
The only way to achieve this with formulas is disable Automatic Recalculation in Excel.
To do this in Excel 2003
1. Click Tools and select Options.
2. Select the Calculation tab.
3. Under Calculation, select the radio button next to Manual
4. Click OK.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
AlKey... don't forget about VBA!!!
You are right that by disabling auto calculation it would prevent updating the cell contents since NOW() will change everytime something is changed on the sheet. But it also disables every other calculation too. If you use the VBA below, it will add the current time in column B, next to any text you enter in column A. No need to disable auto-calc, since the time stamp will be static.
You can use VBA in the specific worksheet's code to do this:
If you need help on where to place this, let me know. It should add the current time to column B in the same row as any text entered in column A.
Please Login or Register to view this content.
- Moo
Last edited by Moo the Dog; 11-25-2013 at 09:51 PM.
Best way is to use vba as Moo has given you.
another option aside from the given two is creating a circular reference.And enabling Iteration.
say date/time will be in B1 down.
in B1 =IF(A1<>"",IF(B1="",NOW(),B1),"") -fill formula down down.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Agree with Moo and Vlady. I did not forget VBA I just now that some users are hesitant to use it
Hi Guys
I really appreciate the quick responses. As being new at excel, I tried your suggestions but I think the major error is somewhere between me and the keyboard. I've uploaded a sample sheet. I hope you can show me where I am going wrong.
Thanks
The VBA seems to work fine, Treecko. Just make sure it is enabled and not blocked due to protection...
- Moo
i don't see any errors.. you have two options there the formula i gave and the vba of Moo.
The vba will display date if entry was made in column A.
maybe you should first delete all the formulas and entry there then try the vba.
Note: delete by columns .select entire column A first using the Header Name "A" then delete .
you cannot have both of the formulas try the formula in another workbook without the vba.
Opps: I missed refresh by 2 min. Moo
Thanks for the reply again Moo. I reopened the file again to make sure it was enabled and make sure the sheet isn't protected. I still not getting the NOW() to auto input into column B when I enter a number in Column A. Not sure if it is something I still haven't checked on/off.
If I don't get back to your reply, sorry, time for bed as I have to work in the am.
Regards
I'm not sure what to say... I have opened the file twice - on my PC at home, and now on my Mac at work. Both times, it asked me if I wanted to enable macros, and I selected Yes. I then deleted the data already in columns A and B, then started adding letters and numbers to column A. Every time I hit enter to input a value in column A, the timestamp of that entry appears in column B. I'll forward the issue on to some other guru's here on the forum to see what they have to say...
- Moo
Hi Treecko
Remove the Formulas from Column B...Moo's Code works as expected for me.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
It sounds like your macro security is set to high or very high.
in excel 2003:
select tools->macros->security
and then switch to medium
You then need to fully close excel (all open workbooks and the application itself) and reopen, you should now get a prompt to enable macros on opening the file.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks