 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008Selecting and keeping todays date
I have a file where people fill out the date of today. If I use TODAY(), I would get the wrong dates from the dates filled out previously. My idea would be that the day of today is filled out when something si enterd in B1. Obviously the day may not change then next day or days ofter that. The reason is that people WILL mistype the data. Is this possible and if so, how? I am working with Excel 2003. houghi  SignatureWe all came out to Montreux Frank Zappa and the Mothers On the Lake Geneva shoreline Were at the best place around To make records with a mobile But some stupid with a flare gun We didn't have much time Burned the place to the ground Use some VBA code? Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub Range("C2").Value = Int(Now) 'Change C2 to the cell reference you want End Sub format thbe date cell as you want the date to show SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I have a file where people fill out the date of today. If I use TODAY(), > I would get the wrong dates from the dates filled out previously. [quoted text clipped - 8 lines] > > houghi Sorry I forgot to say to put the code in the sheet module right-click on the sheet tab and select View Code and select the sheet name. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Use some VBA code? > [quoted text clipped - 19 lines] >> >> houghi > Use some VBA code? > [quoted text clipped - 6 lines] > > format thbe date cell as you want the date to show Thanks, I will try it out tomorow when I am in fomt of the PC with Excel again. houghi SignatureWe all came out to Montreux Frank Zappa and the Mothers On the Lake Geneva shoreline Were at the best place around To make records with a mobile But some stupid with a flare gun We didn't have much time Burned the place to the ground > Use some VBA code? > [quoted text clipped - 6 lines] > > format thbe date cell as you want the date to show OK, perhaps I was not clear with my explanation. I have changed B1 to B2, so the dates are next to each other. However I would need this for the whole column B for the whole column C. So not only would I like the date in C2 the moment I fill something out in B2, but for all and everything in B and C, except B1 and C1 where the headers will be. houghi SignatureLet's not be too tough on our own ignorance. It's the thing that makes America great. If America weren't incomparably ignorant, how could we have tolerated the last eight years? -- Frank Zappa, in 1988 Change the Worksheet_Change Macro to: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub If Target.Row = 1 Then Exit Sub Cells(Target.Row, 3).Value = Int(Now) End Sub And it should do what you want. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >> Use some VBA code? >> [quoted text clipped - 15 lines] > > houghi > Change the Worksheet_Change Macro to: > [quoted text clipped - 7 lines] > > And it should do what you want. Works like a charm. Thanks a lot. This will save me and my cow orkers a lot of time filtering out errors in dates enterd. houghi SignatureLet's not be too tough on our own ignorance. It's the thing that makes America great. If America weren't incomparably ignorant, how could we have tolerated the last eight years? -- Frank Zappa, in 1988 You're Very welcome. Thank you for the feedback. SignatureSandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >> Change the Worksheet_Change Macro to: >> [quoted text clipped - 12 lines] > > houghi How do you ork a cow? Would it hurt me or the cow? Gord Dibben MS Excel MVP >This will save me and my cow orkers a |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.