Home Theater Forum  ›  Forums  ›  Other Diversions  ›  Computers and HTPC  ›  Another Excel Question
Forum Nav More Forums | All New Posts | New Thread

Another Excel Question

#1
Rating: 0
10/12/07 at 9:42am
  • Joined: December 2004
  • Location: KY
  • Post Count: 1,774
Is there a formula in excel that I can use to automatically add the current date, but once it is there, it will not change each day? Example: If I enter data in A1, then B1 will have today’s date entered automatically. Then tomorrow I enter info in A2 and B2 has tomorrows date entered automatically. The date in B1 did not change from today’s date.
Thanks,
Cave Country Weather
Export to Wiki
#2
Rating: 0
10/12/07 at 5:50pm
  • Joined: January 2003
  • Post Count: 783

Re: Another Excel Question

Sure, you'll need to use some visual basic though. This page will help with events and it covers using worksheet|change which is what you'll need to check for A1 changing, then you'll just have B1 change it's contents to today(). Make sure to lock down B1 so it can't be manually edited by mistake later!

http://www.cpearson.com/excel/Events.aspx

Hope that helps

"Computers are a lot like air conditioners - they both work great until you open windows." -Anonymous
"The danger from computers is not that they will eventually get as smart as men, but that we will agree to meet them halfway." -Bernard Avishai

Export to Wiki
#3
Rating: 0
10/13/07 at 6:39am
  • Joined: December 2004
  • Location: KY
  • Post Count: 1,774

Re: Another Excel Question

Thanks for the reply. It looks a little more complicated than I expected, but I will give it a try.
Cave Country Weather
Export to Wiki
#4
Rating: 0
10/13/07 at 1:50pm
  • Joined: December 2003
  • Location: Charlotte, NC
  • Post Count: 2,812

Re: Another Excel Question

I would take a slightly different approach than the one Robert suggests.

It sounds like you will be updating this daily. For instance, today you will put data in A1, tomorrow in A2, etc. That would require some excessively complicated VBA, IMO (unless I'm overlooking something).

I would instead create a button and call it "Date Stamp" or something like that and require the user to click this button after entering that day's data in column A. Put something like this in the button_click event:

Sub Button_Click()
Dim rngDateCell as Range

Set rngDateCell = Range("B65536").End(xlup).offset(1,0)
rngDateCell = format(now(),"Long Date")

End Sub

You should also have some error handling and such in there, but I think that will do what you want assuming you will always use consecutive rows to input the data.
Export to Wiki
#5
Rating: 0
10/15/07 at 8:48am
  • Joined: December 2004
  • Location: KY
  • Post Count: 1,774

Re: Another Excel Question

Seth,
Yes, Data is going to be entered daily on this sheet. This Button, is it located on the tool bar or in the spreadsheet? This spreadsheet is located on a company shared drive and is accessed by multiple users.
Cave Country Weather
Export to Wiki
#6
Rating: 0
10/15/07 at 3:01pm
  • Joined: January 2003
  • Post Count: 783

Re: Another Excel Question

Nah, the the event I suggested can be applied to a range of cells. In this case, your range would just be the singular cell. And it'll update the date cell whenever it's altered, so there's no chance for a mistake in forgetting to press a button.

Although, just like the button you'll need to work up a copy of the code for each cell.

"Computers are a lot like air conditioners - they both work great until you open windows." -Anonymous
"The danger from computers is not that they will eventually get as smart as men, but that we will agree to meet them halfway." -Bernard Avishai

Export to Wiki
Forum Nav More Forums | All New Posts | New Thread