Results 1 to 14 of 14
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    610
    Thanks
    73
    Thanked 6 Times in 5 Posts

    Plug date into cell

    I want to have a button that plugs today's date in mm/dd/yy format into an associated cell. I do NOT want the date to update until the next time I click the button.

    I can't figure out the formula to use. Who can help?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You can use the line of code:
    ActiveCell = Date


    You don't need a button, you can just use the shortcut key:
    ctrl-;

    to add the date to the current cell. [ctrl-shift-; (= ctrl-: ) adds the time to the cell]

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    Lou Sander (2014-08-19)

  4. #3
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Or code to add the date to a specific cell (eg. A7)

    [a7] = Date

  5. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    610
    Thanks
    73
    Thanked 6 Times in 5 Posts
    Thanks! sdckapr and Windows Secrets strike again!

    This has to be the most valuable forum in the world.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    610
    Thanks
    73
    Thanked 6 Times in 5 Posts
    Thanks for the tip.

    I have a big worksheet that contains bank balances for five or six related accounts. When I update the balance, I also update the "Updated On" cell. I was getting tired of typing the same date into five or six cells every time I posted a new balance. Now, thanks to the Lounge, I've broken those chains.

    I get a lot more out of the Lounge that I will ever be able to repay, and I'm constantly looking for ways to support it. I've bought a few e-books, but I wish there were a way just to make a contribution.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    If it is more than one cell, you can also use in VBA something like:
    range("A1:A15")=date

    If using the shortcut key, you can select the cells you want to change the date of:
    ctrl-;
    Then use:
    ctrl-enter
    to fill in all the cells selected.

    Steve

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    Lou Sander (2014-08-19)

  9. #7
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    610
    Thanks
    73
    Thanked 6 Times in 5 Posts
    Probably it's easier to do them one-by-one, but I'll try both ways.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  10. #8
    New Lounger
    Join Date
    Jul 2012
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    I had the exact same need for a button to enter the date. First I created a macro that enters today's date using =TODAY(). To make the date static and prevent it from updating, the macro then copies and pastes it, pasting the value. Finally, the macro formats the date the way I want it. In my case the date format is "d mmm yyyy" but Lou would use "mm dd yy".

    The text of my macro is:
    Sub Date_Enter()
    '
    ' Date_Enter Macro

    '
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "d mmmm yyyy"
    End Sub

    To achieve the "I want to have a button" bit, I put a button on Excel's Quick Access Toolbar details on that procedure can be found elsewhere with a simple search. To use, select the cell, click the button and the date is entered.

    I dare say there is a more elegant way to enter the date and fix it.... ?

  11. The Following User Says Thank You to smithxl For This Useful Post:

    Lou Sander (2014-08-21)

  12. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    There is no need to copy/paste

    Code:
    Sub Date_Enter()
    ' Date_Enter Macro
         ActiveCell.Value = Date
         ActiveCell.NumberFormat = "d mmmm yyyy"
    End Sub

  13. The Following User Says Thank You to sdckapr For This Useful Post:

    Lou Sander (2014-08-21)

  14. #10
    New Lounger
    Join Date
    Jul 2012
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    sdckapr - Thank you. I did not know of the Date value that can used in a macro. Your suggestion is tidier and I have adopted it.

    Lou - the answer to your original question!

  15. #11
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    610
    Thanks
    73
    Thanked 6 Times in 5 Posts
    My, but there are a lot of ways to do what I want to do!

    My, but there are a lot of people who know them and are willing to share their knowledge!

    Thanks to all for the many excellent solutions!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  16. #12
    Lounger
    Join Date
    Nov 2011
    Posts
    26
    Thanks
    2
    Thanked 1 Time in 1 Post
    Lou, Why not link the other cells that you want to show the date to one 'prime' date cell. Then, when you enter the date with
    ctrl-; in the 'prime' date cell, all of the others will automatically update.

  17. #13
    New Lounger
    Join Date
    Jul 2013
    Location
    1586 Tallavana Trail, Havana, FL 32333-5691
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts
    This is a prime example of how like-minded people can share "bits" of information and get "Bytes" in return. Forums are drastically underrated!

  18. #14
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    610
    Thanks
    73
    Thanked 6 Times in 5 Posts
    Williss - I don't always update all the cells at the same time.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •