Microsoft Date And Time Picker Excel

Insert a date picker on the form template. Double-click the date picker that you inserted on the form template. Click the Data tab. In the Data type box, click Date and Time (dateTime).

-->

This section contains information about the API elements used with date and time picker controls.

Overviews

Topic	Contents
About Date and Time Picker Controls	A date and time picker (DTP) control provides a simple and intuitive interface through which to exchange date and time information with a user.
Using Date and Time Picker Controls	This section provides information and sample code for implementing date and time picker controls.

Macros

Topic	Contents
DateTime_CloseMonthCal	Closes the date and time picker (DTP) control. Use this macro or send the DTM_CLOSEMONTHCAL message explicitly.
DateTime_GetDateTimePickerInfo	Gets information for a specified date and time picker (DTP) control.
DateTime_GetIdealSize	Gets the size needed to display the control without clipping. Use this macro or send the DTM_GETIDEALSIZE message explicitly.
DateTime_GetMonthCal	Gets the handle to a date and time picker's (DTP) child month calendar control. You can use this macro or send the DTM_GETMONTHCAL message explicitly.
DateTime_GetMonthCalColor	Gets the color for a given portion of the month calendar within a date and time picker (DTP) control. You can use this macro or send the DTM_GETMCCOLOR message explicitly.
DateTime_GetMonthCalFont	Gets the font that the date and time picker (DTP) control's child month calendar control is currently using. You can use this macro or send the DTM_GETMCFONT message explicitly.
DateTime_GetMonthCalStyle	Gets the style of a specified DTP control. Use this macro or send the DTM_GETMCSTYLE message explicitly.
DateTime_GetRange	Gets the current minimum and maximum allowable system times for a date and time picker (DTP) control. You can use this macro, or send the DTM_GETRANGE message explicitly.
DateTime_GetSystemtime	Gets the currently selected time from a date and time picker (DTP) control and places it in a specified SYSTEMTIME structure. You can use this macro, or send the DTM_GETSYSTEMTIME message explicitly.
DateTime_SetFormat	Sets the display of a date and time picker (DTP) control based on a given format string. You can use this macro or send the DTM_SETFORMAT message explicitly.
DateTime_SetMonthCalColor	Sets the color for a given portion of the month calendar within a date and time picker (DTP) control. You can use this macro or send the DTM_SETMCCOLOR message explicitly.
DateTime_SetMonthCalFont	Sets the font to be used by the date and time picker (DTP) control's child month calendar control. You can use this macro or explicitly send the DTM_SETMCFONT message.
DateTime_SetMonthCalStyle	Sets the style for a specified DTP control. Use this macro or send the DTM_SETMCSTYLE message explicitly.
DateTime_SetRange	Sets the minimum and maximum allowable system times for a date and time picker (DTP) control. You can use this macro or send the DTM_SETRANGE message explicitly.
DateTime_SetSystemtime	Sets a date and time picker (DTP) control to a given date and time. You can use this macro or send the DTM_SETSYSTEMTIME message explicitly.

Messages

Topic	Contents
DTM_CLOSEMONTHCAL	Closes a DTP control. Send this message explicitly or by using the DateTime_CloseMonthCal macro.
DTM_GETDATETIMEPICKERINFO	Gets information on a date and time picker (DTP) control.
DTM_GETIDEALSIZE	Gets the size needed to display the control without clipping. Send this message explicitly or by using the DateTime_GetIdealSize macro.
DTM_GETMCCOLOR	Gets the color for a given portion of the month calendar within a date and time picker (DTP) control. You can send this message explicitly or use the DateTime_GetMonthCalColor macro.
DTM_GETMCFONT	Gets the font that the date and time picker (DTP) control's child month calendar control is currently using. You can send this message explicitly or use the DateTime_GetMonthCalFont macro.
DTM_GETMCSTYLE	Gets the style of a DTP control. Send this message explicitly or by using the DateTime_GetMonthCalStyle macro.
DTM_GETMONTHCAL	Gets the handle to a date and time picker's (DTP) child month calendar control. You can send this message explicitly or use the DateTime_GetMonthCal macro.
DTM_GETRANGE	Gets the current minimum and maximum allowable system times for a date and time picker (DTP) control. You can send this message explicitly or use the DateTime_GetRange macro.
DTM_GETSYSTEMTIME	Gets the currently selected time from a date and time picker (DTP) control and places it in a specified SYSTEMTIME structure. You can send this message explicitly or use the DateTime_GetSystemtime macro.
DTM_SETFORMAT	Sets the display of a date and time picker (DTP) control based on a given format string. You can send this message explicitly or use the DateTime_SetFormat macro.
DTM_SETMCCOLOR	Sets the color for a given portion of the month calendar within a date and time picker (DTP) control. You can send this message explicitly or use the DateTime_SetMonthCalColor macro.
DTM_SETMCFONT	Sets the font to be used by the date and time picker (DTP) control's child month calendar control. You can send this message explicitly or use the DateTime_SetMonthCalFont macro.
DTM_SETMCSTYLE	Sets the style of a DTP control. Send this message explicitly or by using the DateTime_SetMonthCalStyle macro.
DTM_SETRANGE	Sets the minimum and maximum allowable system times for a date and time picker (DTP) control. You can send this message explicitly or use the DateTime_SetRange macro.
DTM_SETSYSTEMTIME	Sets the time in a date and time picker (DTP) control. You can send this message explicitly or use the DateTime_SetSystemtime macro.

Notifications

Topic	Contents
DTN_CLOSEUP	Sent by a date and time picker (DTP) control when the user closes the drop-down month calendar. The month calendar is closed when the user chooses a date from the month calendar or clicks the drop-down arrow while the calendar is open.
DTN_DATETIMECHANGE	Sent by a date and time picker (DTP) control whenever a change occurs. This notification code is sent in the form of a WM_NOTIFY message.
DTN_DROPDOWN	Sent by a date and time picker (DTP) control when the user activates the drop-down month calendar.
DTN_FORMAT	Sent by a date and time picker (DTP) control to request text to be displayed in a callback field. This notification code is sent in the form of a WM_NOTIFY message.
DTN_FORMATQUERY	Sent by a date and time picker (DTP) control to retrieve the maximum allowable size of the string that will be displayed in a callback field. This notification code is sent in the form of a WM_NOTIFY message.
DTN_USERSTRING	Sent by a date and time picker (DTP) control when a user finishes editing a string in the control. This notification code is only sent by DTP controls that are set to the DTS_APPCANPARSE style. This message is sent in the form of a WM_NOTIFY message.
DTN_WMKEYDOWN	Sent by a date and time picker (DTP) control when the user types in a callback field. This message is sent in the form of a WM_NOTIFY message.
NM_KILLFOCUS (date time)	Notifies a date and time picker control's parent window that the control has lost the input focus. NM_KILLFOCUS (date time) is sent in the form of a WM_NOTIFY message.
NM_SETFOCUS (date time)	Notifies a date and time picker control's parent window that the control has received the input focus. NM_SETFOCUS (date time) is sent in the form of a WM_NOTIFY message.

Structures

Topic	Contents
DATETIMEPICKERINFO	Contains information about a DTP control.
NMDATETIMECHANGE	Contains information about a change that has taken place in a date and time picker (DTP) control. This structure is used with the DTN_DATETIMECHANGE notification code.
NMDATETIMEFORMAT	Contains information about a portion of the format string that defines a callback field within a date and time picker (DTP) control. It carries the substring that defines the callback field and contains a buffer to receive the string that will be displayed in the callback field. This structure is used with the DTN_FORMAT notification code.
NMDATETIMEFORMATQUERY	Contains information about a date and time picker (DTP) control callback field. It contains a substring (taken from the control's format string) that defines a callback field. The structure receives the maximum allowable size of the text that will be displayed in the callback field. This structure is used with the DTN_FORMATQUERY notification code.
NMDATETIMESTRING	Contains information specific to an edit operation that has taken place in a date and time picker (DTP) control. This message is used with the DTN_USERSTRING notification code.
NMDATETIMEWMKEYDOWN	Carries information used to describe and handle a DTN_WMKEYDOWN notification code.

Constants

Topic	Contents
Date and Time Picker Control Styles	The window styles listed here are specific to date and time picker controls.

The tutorial shows how to insert a drop-down calendar in Excel (date picker) and link it to a specific cell. You will also learn a quick way to create a printable calendar based on an Excel calendar template.

When working with large or shared worksheets, maintaining data integrity is the biggest problem, especially when it comes to entering dates. Should they be entered as mm/dd/yy or dd/mm/yy or mm-dd-yyyy? And can I simply type a date like '05 Sep 2016'? Oh, and what was the date of the first Monday in September this year?

All of the above problems can easily be solved by inserting a drop down calendar that will let your users fill in dates in a mouse click! This tutorial will teach you an easy way to make such a calendar in Excel, and show how to quickly create a calendar based on a template.

• How to insert calendar in Excel (Date Picker control) - step-by-step instructions to create a drop-down calendar.
• Third-party calendar tools for Excel - a selection of free third-party tools to insert a calendar (date picker) in the desktop and online versions of Excel.
• How to make calendar in Excel - a quick way to create a printable calendar based on a template (weekly, monthly or yearly).

How to insert a calendar in Excel (Date Picker control)

Inserting a dropdown calendar in Excel is easy, but because the Date and Time PickerControl is so well hidden many users don't even know that it exists. The following guidelines will walk you through the process step-by-step, but first be sure to read the following important note.

Note. Microsoft's Date Picker control works with 32-bit versions of Excel 2016, Excel 2013, and Excel 2010, but it will not work on Excel 64-bit.

To insert a calendar in Excel, perform the following steps.

Pick your dates in Excel pop-up calendar

And easily calculate them in place

Read more

Get two-month view of the calendar

Expand the pane to see two months side by side in Excel

Read more

Microsoft Date And Time Picker Excel 2013

Add or subtract the required units in Excel

And see the result right away

Read more

1. Show the Developer tab on the ribbon

The datepicker control belongs to a family of ActiveX controls, which reside on the Developer tab. By default, the Developer tab is hidden in Excel, but you can force it to appear in no time.

• Right-click anywhere on the ribbon, and then click Customize the Ribbon…
• In the right-hand part of the window, select Main Tabs (usually it is selected by default), check the Developer box, and click OK.

2. Insert a calendar control

A drop-down calendar in Excel is technically called Microsoft Date and Time Picker Control. To insert it in your sheet, just do the following:

• Go to the Developer tab > Controls group, click Insert, and then click the More Controls button under ActiveX Controls.
  
• In the More Controls dialog window, find the Microsoft Date and Time Picker Control 6.0 (SP6), select it, and click OK.
  

  If you cannot find the Date and Time Picker Control in the list, please follow these instructions to register it.

• Finally, click on a cell where you want to insert the calendar control.

That's it! A drop down calendar control is inserted in your Excel sheet:

As soon as the datepicker control is inserted, the EMBED formula appears in the formula bar. It informs Excel what type of control is embedded in the sheet, and in no case you should change or delete it, because this would result in the 'Reference is not valid' error.

Inserting any ActiveX control (including DTPiker) automatically turns the Design Mode on allowing you to modify the appearance and properties of the newly added control. The most obvious changes that you will want to make most of the time is to resize your calendar control and link it to a specific cell.

To activate your Excel drop down calendar, go to the Design tab > Controls group, and turn off the Design Mode:

And now, you can click on the dropdown arrow to display the calendar and select the desired date:

Note. If the Microsoft Date Piker control is not available in the More Controls list, it's most likely because of the following reasons:

• You are using the 64 bit version of Office. Regrettably, there is no official date picker control for Office64-bit. The only solution is using a third-party calendar for Excel.
• The calendar control (mscomct2.ocx) is not available or is not registered on your machine. To fix this, please follow these instructions: How to register the calendar control.

3. Customize the calendar control

After adding a calendar control to your Excel sheet, the first thing you may to do is to move it to the desired location and get it to fit into a cell.

To resize the datepicker control, turn the Design Mode on, and drag a corner of the control:

Alternatively, with the DesignMode on, select your calendar control, and click Properties:

In the Properties window, you can set the desired height, width as well as change the font theme and size:

To move the datepicker control, hover your mouse over it and as soon as the cursor changes to a four-pointed arrow, drag the control where you want it.

4. Link the calendar control to a cell

Now that you have successfully added a drop down calendar in Excel, you may also want to link it to a specific cell. It is absolutely necessary if you intend to use the selected dates in formulas.

Let's say, you've written a formula to count the number of orders between the specified dates. To prevent your users from inputting incorrect dates like 2/30/2016, you inserted the dropdown calendars in 2 cells (A2 and B2 in this example). However, your obviously correct COUNTIFS formula returns zero, although you can clearly see a few orders within the specified time period!

The reason is that Excel cannot recognize the value of a date picker control until you associate it with a certain cell. To fix this, link your date picker controls to some cells in this way:

1. With the Design Mode on, select the calendar control.
2. On the Developer tab, in the Controls group, click Properties.
3. In the Properties windows, find the LinkedCell property, and type a cell reference next to it (A3 in this example):
   

If Excel displays the 'Can't set cell value to NULL…' error, just click OK to ignore it.

And now, as soon as you select a date in the dropdown calendar, that date immediately appears in a linked cell. As shown in the screenshot below, Excel has no problem with understanding such dates, and our formula referencing the linked cells (A3 and B3) works perfectly:

If you don't want to clutter your worksheet with 'extra dates', you can link your date picker controls to the cells where they reside (A2 and B2 in this example). In this case, the underling dates won't be visible to the users, but you and Excel will know they are there and your formula will work faultlessly again:

How to register the Calendar control on your machine

Microsoft Date And Time Picker Excel

In earlier versions of Excel, the Date and Time Picker Control (mscomct2.ocx in Excel 2010 and 2013 and mscal.ocx in Excel 2007) was usually included with the default installation. The users of Excel 2016 will have to download and register this control manually. Here's how:

1. Check if mscomct2.ocx exists on your machine

For starters, check if the Microsoft Calendar Control is available on your machine. To do this, click the Start button (Office button), type mscomct2.ocx (or mscal.ocx in Excel 2007 and earlier) in the Search box, and click Enter.

If the mscomct2.ocx file is found, skip the next step, and proceed with step 3.

2. Download Microsoft Date and Time Picker control

If the Microsoft Date Picker Control is not found on your machine, you can download it using this link. Please remember, this control works only for the 32-bit version of Excel 2019, 2016, Excel 2013 and Excel 2010.

Once the mscomct2.zip file is downloaded, extract mscomct2.ocx, and copy it to one of the following folders depending on your Windows' bits:

• On Windows 32-bit: C:WindowsSystem32
• On Windows 64-bit: C:WindowsSysWoW64
  Tip. A quick way to check the operating system information is to open Windows Explorer, right click Computer, and click Properties.

You must have the administrative permissions to copy files to the system folders. If you do have the admin permissions, but still get the Destination Folder Access Denied dialog, simply click Continue:

3. Register the calendar control in your system

Not only the calendar control (mscomct2.ocx) should exist on your computer, it should also be registered in the system. To have it done, perform the following steps:

1. Click the Start button and type 'command' in the search box, but don't press the Enter key.
2. Right click Command Prompt at the top of the search results, and then click Run as Administrator.
   
3. In the Administrator dialog box, enter one of the following commands, and then press Enter:

   For Windows 32 bit:

   C:WindowsSystem32regsvr32.exe mscomct2.ocx

   For Windows 64 bit:

   C:WindowsSysWoW64regsvr32.exe mscomct2.ocx
   

   Note. By default, the copy/paste shortcuts are disabled in the command prompt window. To paste the command, position the cursor where you want the text to be inserted, then right-click on the command prompt's title bar, point to Edit, and click Paste.

Alternatively, you can copy/paste the command in any text editor such as Notepad, save the file with the .bat extension (e.g. reg.bat) anywhere on your computer, right-click the .bat file, and then click Run as Administrator.

Either way, mscomct2.ocx should be successfully registered on your machine.

Once mscomct2.ocx is registered, restart your Excel, go to the Developer tab, click Insert > More Controls, and Microsoft Date and Time Picker Control will be there.

Note. Although you can register the calendar control for Windows 64 bit, it will work for Office 32 bit only. Even if you add a reference to mscomct2.ocx/mscal.ocx in Excel 64 bit, it will still return the 'Could not load an object because it is not available on this machine' error.

Third-party tools to insert calendar in Excel

As already mentioned, Microsoft's Date and Time Picker controls work only in the 32-bit versions of Excel. If moving from 64-bits to 32-bits is not your option, third party tools are the only working solution. Below you will find a few calendars that work with both 32 bit and 64 bit versions of Excel 2019, Excel 2016, Excel 2013, and lower. It would be only natural if we start with our own calendar :)

Ablebits Date Picker for Excel

The users of the Ultimate Suite can find their Excel drop-down calendar on the Ablebits tab in the Date & Time group. The Date Picker is turned off by default, to turn it on, just click this button:

Once the Date Picker is activated, a small calendar button appears on the sheet when you type or select a date in a cell. If necessary, you can change date format as usual and the calendar will supply dates in your custom format - a nice feature that Microsoft's Date and Time Picker control lacks. Additionally, the calendar shows how far away the selected date is from today's date. Also, it allows you to quickly get to today's date or switch between one-month and two-month views:

Moreover, you can also calculate dates right in the calendar! For this, select the target date in your worksheet or in the calendar, and click the Date Calculator button in the upper right corner. Then, click the desired unit on the upper pane and type how many years, months, weeks or days (or any combination of these units) you want to add to or subtract from the target date. After that, press the Enter key to insert the result into the currently selected cell or press F6 to display the calculated date in the calendar (or click one of the buttons shown in the image below).

As an example, let's add 1 year and 60 days to April 1, 2018:

If you don't have our Ultimate Suite in your Excel yet, you can download a trial version here, or you can go with one of the free drop-down calendars listed below.

Excel Date Picker

Excel Date Picker from samradapps.com is a beautifully designed and easy to use calendar that supports all versions of Excel 2007 to 2016.

It adds the Date Picker button to the ribbon, and optionally, an item to the right click menu. And I confirm that it works without a hitch in my Excel 2016 64 bit.

Pop-up Excel Calendar

It is another free pop-up calendar for Excel 2003 to 2016. Although support for 64 bit Excel is declared by the manufacture, it throws sporadic errors in my Excel 2013 and 2016 64 bit.

In all other respects, it's a handy little tool that displays a calendar whenever needed - for cells containing date values, date formats or certain labels.

Pop-up calendars for Excel Online

Microsoft Date And Time Picker Control Excel 2010 64 Bit

If you often manage time tables, schedules and to-do lists in Excel Online, you may find the following Calendar add-ins helpful.

XLTools.net Calendar - one inserted, the below calendar appears on the sheet enabling you to enter or change a date in a click.

Modern Calendar & Date Picker - a pop-up calendar that works with the desktop versions of Excel 2013 and 2016, Excel 2016 for Mac, and Excel Online. It includes dozens of themes for you to choose from and costs around $1.50.

Mini Calendar and Date Picker - another low-cost calendar for Excel Online, Excel 2013, Excel 2016 and Excel 2016 for Mac. It allows you to toggle the size of the calendar, show week numbers, switch between the U.S. or ISO calendar systems, and choose different themes.

Microsoft Date And Time Picker Control Excel 2019

How to make a calendar in Excel based on a template

In addition to spreadsheets that can calculate nearly everything, Microsoft Excel includes a handful of graphic options with drawing tools, clipart, charts, tables, etc. Using these features, you can easily create a weekly or monthly calendar with your own photos and special occasion days such as birthdays or anniversaries. The detailed guidelines to create a custom Excel monthly calendar can be found here.

However, the fastest and easiest way create a calendar in Excel is to pick a free Excel calendar template. For this, just click File > New, and type 'calendar' in the search box. Excel will search through thousands of templates online and show you a selection of daily, weekly, monthly and yearly calendar templates grouped into categories, like shown in the screenshot below:

Select the calendar template that you liked the most, and click Create:

That's it! The calendar template will open up in a new workbook, and you can print it out straight away or customize to your liking. Generally, an Excel calendar template can be set to any year, some templates also allow setting a day to start the week.

Tip. To ensure that your Excel calendar always displays the current year, you can enter the following simple formula in the year cell: =YEAR(TODAY())

Well, this is how you can insert a drop-down calendar and make a printable calendar in Excel. Hopefully you will find the information in this tutorial helpful. Anyway, I thank you for reading and hope to see you on our blog next week!

Microsoft Date And Time Picker Control Excel 2016

You may also be interested in: