Inserting Today's Date in One Click!

Feb 3, 2021

Clinical Research Data 101

Tools/Languages: Excel VBA Macro

This blog post shows the VBA macro you need to insert today's date by clicking a bottom.

This tutorial is to enlighten those of you who are tired of typing dates in Excel.

First of all, here's the link to learn how to turn on the "Developer Mode" in your Excel.

In clinical research, we need to monitor the enrollment progress every day and enter the new participants to our tracking database, aka Excel spreadsheet. However, Excel doesn't come with the little calendar attached to the cell if you change the cell format to "Date". Therefore, you end up typing or copying/pasting the dates over and over again. That's annoying!

What happened?

With these few simple lines of codes, you will be able to enter the dates without typing anything. You can place your mouse in any cell and click the bottom. BOOM! Today's date is entered like magic.

What should I do now?

• Open the Excel spreadsheet and turn on the "Developer" mode as described at the beginning.
• Click "Visual Basic" on the top ribbon (first one on the left).
• Insert a new Module and copy/paste the codes below.

  
  Sub today()
  '
  ' Today Macro
  
  ' Insert today's date
  '
  ' Keyboard Shortcut: Ctrl+Shift+D
  '
  
  ActiveCell.FormulaR1C1 = "=TODAY()"
  
  ActiveCell.Select
  
  Selection.Copy
  
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
  
  ActiveSheet.Paste
  
  Application.CutCopyMode = False
  
  End Sub
  						

• Go back to the spreadsheet and click the "Bottom" icon on the top ribbon. Then assign the macro to "today" and click "OK".
• Place the macro bottom somewhere accessible on the spreadsheet.

Please leave me a message if it doesn't work for you.