Best Date Navigator for Users and VBA Developers

As an Excel user, how often do you experience frustration entering dates or changing dates in Excel? You have probably experienced “date entry identity confusion” when Excel changes values you think should be a date into something else, or a date you think should be one date into another completely different date.

As an Excel developer, how many times have you tried to find that elusive Microsoft “date and time picker” or “calendar” control to use on worksheets and user forms, only to end up down a rabbit hole while searching the Internet for clues on where it is and in what product it might have been included that you might have installed. And then, when you do find it, you struggle with its archaic and obtuse properties, trying to get it to behave in some fashion conducive to the workflow you are trying to create and the nuanced behavior of your users. Once found and dialed in as best it can be, then you have to worry about whether it will be present in the next Office release! And that’s just Is that working for you?

Data Automation Professionals long ago gave up playing the Microsoft date-and-time-picker-calendar-something-or-other shell game and we created our own date entry control. We call it Date Navigator and, having used it in countless projects and improving it over the years to meet every need we encountered, we are offering it to the public. In short, it’s an incredibly powerful tool that anyone entering and changing dates in Excel will find invaluable.

Elegantly Simple

If you have used the Microsoft calendar controls (MSCAL.OCX or the later MSCOMCT2.OCX) you know they are limited in functionality, sport decades old user interfaces, and are fraught with compatibility issues including missing components and cryptic run time errors. Many third parties have tried to fill the gap and provided their own versions of the calendar control, but they have limited functionality, use old school user interface techniques, try to be more than just a date entry control, and/or present excessively complex and colorful interfaces.

Our version of the calendar control, Date Navigator, is just a date entry control—nothing more and nothing less. It presents a calendar in as simple a form as possible with absolutely no extra fluff to distract from the task of finding and selecting a date. Workdays in bold font with holidays in green and today in red—no amateur red “hand drawn” circles in our tools!

Awesome Power

While elegantly simple, Date Navigator comes with an incredible array of powerful functions just under the surface. In one scenario, a date is selected with a single click and Date Navigator is gone. In another scenario, various dates are considered while effortlessly moving across weeks, months, and years, knowing exactly how many calendar and/or working days are between today or a selected date and the date under the mouse cursor.

Don’t like clicking buttons? An extensive array of keyboard commands are available to navigate and select. Or, of you’re a mouse wheel kind of power user, use the wheel to scroll though the months and years to find your date.

Flexibility for the User and the Developer

As a user, you can quickly configure Date Navigator’s single window to show one, two, three, six, or twelve months and an optional status area presents information about the selected date such as how many calendar days, working days, or weeks it is before or after today or the selected date. You can also configure how many months and years are displayed in the jump menus. Your settings are saved in the registry to you don’t have to reset them every time you open Excel.

While we present a minimal set of navigation controls to move the view by month and by year, there are an extensive number of keyboard and mouse commands that provide a plethora of ways to move the view from month to month and year to year, and change the selected date by calendar date or working date. A text-based keyboard entry feature provides unprecedented ability to instantly navigate to specific dates or dates a specific number of days, weeks, months, and or years in the past or future.

A status area at the bottom of the window displays the relationship between any date and today or the currently selected date. All of this makes Date Navigator the quintessential “date picker” for any Excel user.

Date Navigator in Action

Single month view:

Six month view:

Twelve month view:

Moving the mouse over a date displays information about that date in the status area:

Move the mouse over controls to display how to use those controls along with other features such as keyboard shortcuts:

Right-click on a month header to display a jump menu of past and future months:

The Rest of the Story

We provide Date Navigator as an Excel Add-In and as source code. In source code form you can include it in your Excel project and never have to worry about where that date control is or if your code is compatible with it—it’s always available regardless of what Excel version is running, the user interface and API never changes unless you want to change it, and it provides your users with unparalleled Excel date entry and Excel date editing power.

Date Navigator is displayed using a VBA call to a single API function which accepts the currently selected date and returns the date the user selects. Call Date Navigator when a cell is selected, a button on a worksheet is clicked, or a control on a user form gets focus or is clicked—you decide when and how it appears, and how it behaves.

Features Defined via the API

  • Specify which days of the week are work days.
  • Specify any number of holidays.
  • Specify if the user selects a date with a single click or a double click.
  • Specify the color of the controls (grey or blue.)
  • Specify the color of the headers.
  • Specify the position of the window (the default is at the current mouse position.)
  • Specify the window’s transparency.
  • Specify the earliest and latest date displayed (the default is all dates from 1/1/1900 to 12/31/2099.)

User Controllable Features

The following features are controlled by the user and are saved in the user’s registry settings.

  • Whether or not the status area is displayed. The default is displayed.
  • How many months are displayed. The default is three.
  • How many months are displayed in the navigation pop up menu. The default is 12.
  • How many years are displayed in the navigation pop up menu. The default is 10.

Specifications and Requirements

  • Runs in 32 and 64-bit modes.
  • Compatible with Office 2003 and later.
  • Add-In runs in Excel only.
  • Source code has not been tested in applications other than Excel.
  • Has not been tested on iOS.
  • Requires VBA compatibility (will not run in Office Online.)
  • The source code is all VBA and contained in three VBA modules: a general code module, a user form module, and a class module. There are no external files.

Next Steps

Curious? Interested in purchasing? Use our contact form and select “Date Navigator”.

See Also

Date Navigator Help

Date Navigator API