How do I set Excel to change the date automatically?

  • 8 Replies
  • 35913 Views

0 Members and 2 Guests are viewing this topic.

*

Offline ukmicky

  • Neilep Level Member
  • ******
  • 3011
    • View Profile
    • http://www.space-talk.com/
Please please please I'm hooked on and trying to teach myself excel and need help doing something .

I can change the date automtically in excel by typing in =today() in cell B2

However i would be prefer to change the date in B2 by creating a text box/ button so it updates only when i click on the button.


Howwwwwwww



« Last Edit: 25/04/2008 22:50:08 by chris »

*

Offline DoctorBeaver

  • Naked Science Forum GOD!
  • *******
  • 12656
  • A stitch in time would have confused Einstein.
    • View Profile
Re: How do I set Excel to change the date automatically?
« Reply #1 on: 23/04/2008 23:23:54 »
If I remember correctly, you can do it using events and form controls. I don't use Excel any more (OpenOffice rules!), but I'm sure that's how it's done. Check out macros, events, triggers and form controls in the help system.
Fledgling science site at http://www.sciencefile.org/SF/content/view/54/98/ needs members and original articles. If you can help, please join.

*

Offline MonikaS

  • Sr. Member
  • ****
  • 279
    • View Profile
Re: How do I set Excel to change the date automatically?
« Reply #2 on: 24/04/2008 13:27:35 »
You need to write a small macro:

Sub UpDateDate()
'
' UpDateDate
' made by MonikaS
'
      D = CDate(FormatDateTime(Now, vbShortDate))
      Range("B5").Value = D
End Sub


Attach this to a graphic on your spreedsheet and each time you click the graphic the date will change to the current date in cell B5.

HTH
Monika
--------
I tried installing Jesus on my Scientific Method and I got a Blue Screen of Eternal Damnation.

*

Offline ukmicky

  • Neilep Level Member
  • ******
  • 3011
    • View Profile
    • http://www.space-talk.com/
Re: How do I set Excel to change the date automatically?
« Reply #3 on: 24/04/2008 22:27:00 »
You need to write a small macro:

Sub UpDateDate()
'
' UpDateDate
' made by MonikaS
'
      D = CDate(FormatDateTime(Now, vbShortDate))
      Range("B5").Value = D
End Sub





Attach this to a graphic on your spreedsheet and each time you click the graphic the date will change to the current date in cell B5.

HTH
Monika
Thankyou i will try it

*

Offline DoctorBeaver

  • Naked Science Forum GOD!
  • *******
  • 12656
  • A stitch in time would have confused Einstein.
    • View Profile
Re: How do I set Excel to change the date automatically?
« Reply #4 on: 25/04/2008 08:24:21 »
You need to write a small macro:

Sub UpDateDate()
'
' UpDateDate
' made by MonikaS
'
      D = CDate(FormatDateTime(Now, vbShortDate))
      Range("B5").Value = D
End Sub


Attach this to a graphic on your spreedsheet and each time you click the graphic the date will change to the current date in cell B5.

HTH
Monika

Yeah, you can do it that way if you really must  [:P]
Fledgling science site at http://www.sciencefile.org/SF/content/view/54/98/ needs members and original articles. If you can help, please join.

*

Offline MonikaS

  • Sr. Member
  • ****
  • 279
    • View Profile
Re: How do I set Excel to change the date automatically?
« Reply #5 on: 25/04/2008 09:30:58 »
Well, he wanted a button to click on [;D]
Without a button Crtl . (it's a dot) would insert the current date using the default datetime format.
Monika
--------
I tried installing Jesus on my Scientific Method and I got a Blue Screen of Eternal Damnation.

*

Offline DoctorBeaver

  • Naked Science Forum GOD!
  • *******
  • 12656
  • A stitch in time would have confused Einstein.
    • View Profile
Re: How do I set Excel to change the date automatically?
« Reply #6 on: 25/04/2008 22:42:32 »
Well, he wanted a button to click on [;D]
Without a button Crtl . (it's a dot) would insert the current date using the default datetime format.
Monika

Button, Schmutton... oy vay!
Fledgling science site at http://www.sciencefile.org/SF/content/view/54/98/ needs members and original articles. If you can help, please join.

*

Offline ukmicky

  • Neilep Level Member
  • ******
  • 3011
    • View Profile
    • http://www.space-talk.com/
How do I set Excel to change the date automatically?
« Reply #7 on: 26/04/2008 03:05:23 »
Thankyou MonikaS

It works brilliantly ,now i can click on a transparent box and it changes the date underneath  [8D]

Dont worry doc i know you would have figured it out for me sooner or later [::)]


*

Offline DoctorBeaver

  • Naked Science Forum GOD!
  • *******
  • 12656
  • A stitch in time would have confused Einstein.
    • View Profile
How do I set Excel to change the date automatically?
« Reply #8 on: 26/04/2008 16:52:11 »

Dont worry doc i know you would have figured it out for me sooner or later [::)]


I doubt it as I don't use Excel. I switched to OpenOffice a couple of years ago. I don't even have Excel on my PCs anymore.

I was sent an Excel spreadsheet a few days ago - 42k in size. I loaded it into OpenCalc and saved it in .ods format - 17k. That's 1 of the reasons I don't use Excel.
« Last Edit: 26/04/2008 16:54:54 by DoctorBeaver »
Fledgling science site at http://www.sciencefile.org/SF/content/view/54/98/ needs members and original articles. If you can help, please join.