You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s author: Sam Radakovitz, a program manager on the Excel team that enjoys creating VBA applications in Excel.
Back in early 2005, Excel 2007 was under development, the Backstreet Boys released a new album, and I took a weekend out to create a date picker add-in for Excel. The add-in was fun to write, not super duper fancy code, but it got the job done and helped me learn about the various Excel events and Excel’s add-in model. I pushed it up on the web, got some feedback and made a couple of iterations on it, and by the end of the year I had wrapped it up and moved on to other things.
Recently this add-in got demo’d at a Microsoft event and it made it back on my radar. I decided to write a blog post about the add-in since some folks might find it useful … or at least spark some conversation about date pickers in Excel. If you want to try the add-in for yourself, you can download it and the sample file I’m going to use for this blog post here:
After downloading the add-in, you can stick it in your XLSTART folder (mine is at C:\Program Files\Microsoft Office\OFFICE12\XLSTART) and restart Excel for it to load.
The sample file contains a small list of projects for a department store. I’ve made the list small and removed some columns of information to keep things simple. With the date picker add-in installed, choosing a date in a cell will put an icon next to the date:
This is the entry point we will use to view the date picker, there are other entries points but I’ll get into them a bit later in the post. Clicking on the date picker icon will display the calendar:
The calendar fades in and defaults to the date that is in the selected cell. The green highlighted day represents where your mouse is. When you move the mouse to a different day, you’ll notice a few things change:
The green highlight moves with the mouse. The blue highlighted date, in this case January 1st, represents the selected cell’s value. As the mouse moves, the status text at the bottom tells you the date difference from the selected cell’s value. In this case we’re hovering over January 15th, so the status text is saying we’re going to move the date forward 14 days if we click on it. You can change how the status text displays the difference by clicking on the text:
Now instead of 14 days it says 2 weeks. Clicking on it will cycle through a few different groupings. To change the month displayed or the year, you can pick from the combo box at the top of the calendar or change the months by clicking on the left or right edge of the calendar:
Clicking the options button in the upper right of the calendar will fly out the options pane. Here you can specify the entry points you want visible and turn on and off the fading of the date picker:
Choosing the “In Cell Icon Settings…” button will display a popup of the different icons for the date picker control and give you additional options on where you want the icon position in the cell:
The cell icon will display when a date is selected, but there are other times it will display too. Below is a quick list of the entry points to the date picker:
The last thing I want to mention about the date picker is the warning text. If you have a formula (or other various things) populating the cell, picking a date will overwrite it. To help inform folks that the date picker is going to blow away the formula for the cell it will display some warning text on it:
And that’s about everything this date picker can do in a nut shell. There are some enhancements I’d like to make, not sure if I’ll get to them though:
If you have any ideas or comments, or a date picker you love to use, please post them here!
Are you going to include this in the next Excel version?
Joe Curious - I don't think we would include this exact add-in if we did something ... and if we do something still remains to be seen, not sure if we know the demand for a feature like this (David Gainer would know better than I).
your date picker is very useful!
Your date picker looks interesting so I was really interested in reading and learning from your code - but it seems like the vba project is password protected? Any chance that will change?
I like the warning text idea. It also shows you've fully thought through the various usage scenarios.
My only gripe is a minor one - the look and feel doesn't gel in with the rest of the Office 2007/Ribbon look.
d.volans - thanks!
arun.philip - thanks! I haven't touched the UI or colors since 2005, so I haven't really updated it to match with all the new fancy Office 2007 stuff.
Charles Miles - Possibly ... I'll have to dig up the source, I haven't touched it in a while, so I think its on an external HD somewhere at home. On creation, I type in a long random password and obfuscate the code. Maybe the source could be another blog entry.
Sam, thanks for this useful date picker. For the next version of Excel, I'd like to see a date picker incorporated into Data Validation. A date picker would be a natural pop-up for an input cell(s) with a validation criterion of "Date."
There is also a nice calender utility that navigates with keyboards too from below site of Shah.
date picker user
date picker is great! just a minor remark:
In the date picker, if one selects the month drop down, the currently selected month (i.e. august) is highlighted. But if one changes one's mind at that point, or if you have clicked on the month drop down by error, you cant exit the drop down while keeping august selected. (datepicker apparently waits for a new input)
I'd echo the previous comments that this looks impressive, and add that I've seen requests for datepickers so many times on Excel forums, that this should either be included in future versions, or made widely available.
Seeing the source code would also be a big help, as I've recently built something similar so that my users wouldn't have to enter dates manually into userforms.
This is great!
however, a date changed by the date picker does not apear in the undo list as far as I can see, which makes it something I would not give to some users. Changing the wrong date in (say) a forecasting tool could have huge consequences, being able to use "undo" is pretty vital. If it's too hard to do this through the built in feature (which I would find odd) then an internal system to track these changes and provide a choice in the pop-up window of "revert to previous value" would be perfect.
Great tool - I like its ability to show "Back" and "Forward" number of days away from the current date.
This would be a handy tool for Outlook Appointments!
One point of criticism: I'm not fond of advising people to put the file in XLSTART.
I would have built a real addin (.xla) as opposed to a hidden xls so you can enable it when you need it in Tools, Addins.
I hope you do show the source at some point - always educational to look at the code for useful interesting projects like yours.
I am curious why you "type in a long random password and obfuscate the code" on creation of the project - do you do that on every project? Just curious!
Colin Banfield - nice, I would imagine it in data validation as well.
date picker user - thanks for the link!
chris h - thanks! I'll have to fix that if I ever get to editing it again.
Neil - thanks!
AdamV - thanks! yeah I should add to the undo stack, I'm not sure how to off the top of my head, but I remember seeing example VBA code somewhere that did it.
Rich Williams - I'll mention it to the outlook team!
Jan Karel Pieterse - thanks! it is an XLA, so if you want to put it in a different directory and load it through the add-in manager it should work. XLSTART is just how I was doing it. Though for some reason when i goto download the XLA IE says its an XLS ... ?
Charles Miles - only some projects, I don't think there was a special reason for it back then (at least I can't remember one).