Excel Time stamp issue

I need to place a time stamp in A1 when I place a check in B2. But I can't figure out how to keep the timestamp (I'm using a NOW() formula) from constantly updating every time I do anything on the page. Is there a way to freeze the time stamp?
Josh EhlersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlanConsultantCommented:
Hi Josh,

The only way to fix it is to either enter it manually (Ctrl + ;) or (Shift - Ctrl - ;) for date and time respectively, or to use VBA by putting something like this in the worksheet code for the appropriate sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B2").Value = "X" Then

    Range("A1").Value = Now()
   
End If

End Sub

Open in new window


Would either of those work for you?

Alan.
0
Josh EhlersAuthor Commented:
I'm attempting to make everything automatic...so ctrl+shift+; is no good. How would you do it in VBA?
0
AlanConsultantCommented:
Hi Josh,

As above, and see attached.

I edited just before you posted, so you probably didn't see it.

Alan.
EE-29089807-Version1.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Josh EhlersAuthor Commented:
Hi again Alan,

I thank you for your help, would you take a look at this to see/understand what I'm attempting and see if it can be done?  Basically if F13:L14 receive a check I need a timestamp in B13
binary.xlsx
0
AlanConsultantCommented:
Hi Josh,

Does the attached work for you?

I am not sure what you mean by 'Check' so I have used 'X' but easy to change to whatever you want.

Alan.
EE-29089807-Version3-binary.xlsx
0
Josh EhlersAuthor Commented:
Hi Alan,

Sorry, when I say check I mean a "P" I'm just changing the font to make it a check. Unless there's a setting preventing me from seeing what you did. I don't understand what you've done. Nothing I'm doing is creating a time stamp
0
AlanConsultantCommented:
Hi Josh,

I have now made it so that if you enter a 'P' (Capital p) into any of the cells in the Columns F:L, then a timestamp will appear in Column B, and be fixed there (unless you manually edit or delete it).

Does that work for you?

Alan.
EE-29089807-Version5-binary.xlsm
0
Josh EhlersAuthor Commented:
Hi Alan,

That is amazingly perfect! I've been trying to figure out this issue for months! Then I came across someone that recommended this website. Ok, so two more questions for you. One, I still need it to place a timestamp in one more area. If you look in B11 I need a starting time stamp. Basically once I place and "X" or "P" in F13 or F14 it needs to place a time stamp in B11. My second question, how did you do what you did? Is there a book that you could recommend if that would be easier? I don't know a whole lot about VBA

Josh
0
AlanConsultantCommented:
Hi Josh,

It now places the additional timestamp in B11 when a 'P' is entered in either F13 or F14.

To see the code, from your worksheet, press Alt-F11, then double click on Sheet1 in the left hand pane, and you will see the code on the right.

It could be much shorter, but I have gone for 'simple' since you are new to VBA, so that if you need to maintain it or change anything, hopefully it will be easy for you to work out what to do.

For me, I just taught myself by knowing I wanted to do something, and implementing it (starting simple, such as this).  I can't remember now how I started - it was around early to mid 90s and I cannot remember much from that far back :-)

Another option is to learn by editing someone else's code - this might be a good start for you too.

There are loads of books out there, but for me, nothing works as well as the incentive of not having to waste time doing repetitive tasks, and VBA is great for avoiding that in Excel.


Hope that helps,

Alan.
EE-29089807-Version6-binary.xlsm
0
Josh EhlersAuthor Commented:
Hi Alan.

That is absolutely perfect!  I can't thank you enough

Josh
1
AlanConsultantCommented:
You're most welcome.

Don't forget to close of the question.

Thanks,

Alan.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Josh EhlersAuthor Commented:
Alan solved my issue in just minutes. I can't thank him enough
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.