Excel Tip: Minimize date entry errors using a calendar drop down list

Posted by EQ Tech on Fri, Jul 10, 2015

Ensuring data integrity by limiting date entry errors in a Microsoft® Excel® worksheet can easily be achieved by adding a calendar drop down list.  So, instead of entering dates manually, you can select them from the list. In ten simple steps, we show you how you can add a calendar drop down list using only control buttons and no VBA code.

Note: Download the sample workbook to practice this exercise.

Applies to: Microsoft Excel 2007, 2010 (32-bit) and 2013 (32-bit)

The screen shot below will be used for this example.

1 15 2014 3 41 44 PM

 

1.  Activate the developer tab.

     a. In Microsoft Excel 2010:

  • Select the File tab.
  • Select Options.
  • Select Customize Ribbon.
  • Tick the Developer box and select OK as per the screen shot below.
  • The Developer tab will now appear on the ribbon.
1 15 2014 3 43 04 PM

     

     b. In Microsoft Excel 2007:

  • Select the Office button
  • Select Excel Options
  • Select the Popular tab
  • Tick the Show Developer tab in the ribbon check box
  • The Developer tab will now appear on the ribbon.

2. Select the Developer tab.

3. Select Insert. A drop down list with icons for Form Controls and ActiveX Controls will appear.

4. Select the icon on the bottom right hand corner under the ActiveX Controls.

1 15 2014 3 44 05 PM

 

5. More Controls will appear. Select Microsoft Date and Time Picker Control 6.0 (SP4)

6. Select OK.

1 15 2014 3 44 44 PM

 

7. Your cursor will turn into a cross hair. Click and hold, draw a rectangle in cell C3.

8. Right-click on the Date Control and select Properties.

9. Then type the cell where the date will be displayed next to linked cell as below.

1 15 2014 3 45 31 PM

 

  • Ignore any error that you might get by clicking OK on the error message dialog box.
  • Close the properties window.
  • Select Design Mode to turn it off as per the screen shot below.
1 15 2014 3 46 13 PM
10. When you select the date from the drop down menu the date in cell C4 will change.

1 15 2014 3 46 53 PM

 

Wow, you are really good at Excel now!

 

For more Sage 300 and Excel Tips, subscibe to the Equation Technologies Blog!

 

Topics: Sage Intelligence and Financial Reporting

Submit a Comment

Need Help Now?

Request a Call