I'm entering tons of dates into Excel - all for the same month and year just different days. Is it possible to simply enter "20" into a cell and have it populate as "12/20/2013" ?
2 Answers
Enter the number in one column (eg A), and in the next column use DATE
:
=DATE(2013,12,A1)
Fill down to work with other rows.
You can also use a VBA macro to do this, like this:
- Open VBA editor by pressing Alt+F11.
- Select from menu Insert > Module.
Write below code:
Sub dateMe() On Error GoTo NotPossible If CInt(ActiveCell.Value) > 0 And CInt(ActiveCell.Value) < 32 Then ActiveCell.Value = CDate("2013-12-" + CInt(ActiveCell.Value)) End If NotPossible: End Sub
Now you can open Macro window by selecting View > Macros > View Macros from menu, and now when
dateMe
is selected press [ options... ] and select a key for your short-cut key.Note: short-cut key or running a macro over your selected cell will run after exiting from Edit Mode or While Wrinting Inside Cell.