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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.
Josh EhlersAuthor Commented:
I'm attempting to make everything automatic...so ctrl+shift+; is no good. How would you do it in VBA?
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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
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
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
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
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
Josh EhlersAuthor Commented:
Hi Alan.

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

Josh
AlanConsultantCommented:
You're most welcome.

Don't forget to close of the question.

Thanks,

Alan.

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
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.