Hardware

Enter date values quickly using these Excel tricks

Susan Harkins explains how to use these simple tricks to enter date values quickly and intuitively into Microsoft Excel.

There are a number of quick tricks for entering dates, especially if there's some kind of pattern to the date values you're entering. Some of them you already know. For instance, you can enter the current date by pressing [Ctrl]+; (that's a semi-colon character). You can then create a date series by copying that single date using the fill handle. However, that process is a bit limited. Using the fill handle increases the date by one day. So, if you enter 8/8/11 and use the fill handle to create a date series, you end up with 8/8/11, 8/9/11, 8/10/11, and so on.

There's a lot of built-in flexibility to the fill handle, if you know how to access it. Using the left mouse button - the normal process - creates the incrementing day series. If, on the other hand, you use the right mouse button to drag the fill handle, you'll get a whole lot more. Let's try that now:

  1. Select a cell and press [Ctrl]+; to enter the current date.
  2. Using the right mouse button, drag the fill handle down several cells and release. When you do, Excel displays a context menu.
  3. Select the appropriate option. For instance, choose Fill Months to increase the month value in your date series.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
dyurasovich
dyurasovich

Good tips, however, I'm stuck on how to sort by month and date when given the date as mm/dd/yyyy. Is there a way to isolate the month, day, and year?

jharvie673
jharvie673

Ctrl+Shift+: colon) gives the current time.

m.d.barron
m.d.barron

If you hold down the Ctrl key while dragging the fill handle, the date will not increment.

ssharkins
ssharkins

I right-click everything. :) Sometimes it does nothing, sometimes... Surprise!

allysonstewart
allysonstewart

Another great tip, Susan! Excel's fill feature is one of my favorites. I use it often when I'm creating budgets or documents that require dates. Thanks for sharing that we can right-click to access the fill options faster. Have a great day.

berth
berth

I create a formula using =Month(A4) where A4 is where the full date (08/16/11) is located. This will show the number 8 in that particular formula column. I then copy the formula for the entire column and then sort the particular area that you want sorted by month. You should check out the =Year(A4) and =Weekday(A4) as well, if you are looking for a particular day of the week. I've found adding these columns comes in hand when creating pivot tables too. Good Luck Dyuraovich!

hulyalkar
hulyalkar

Two options: use data to text feature in Data ribbon and use "/" as separator. Sort on month and use CONCATENATE function join in mm/dd/yyy and more simple way is use customs sort and selct Jan, Feb, Mar as sort order. You can also use DATE, MONTH and YEAR function to separte date or extract any one component from date cell

Editor's Picks