Archer Dynamics

We’ve all witnessed it: Your esteemed colleague from accounting, clicking through the month view dozens of times to reach December 2027. Or worse – their colleague typing by hand “2/30//2026” and wondering why the formula breaks. Today, we end the madness with two solutions that’ll make date entry actually pleasant and usable.
And user-proof.

Calendar picker aka The Easy Button

I’m sad to report that there isn’t one. Used to be. But the latest 64-bit version of Excel no longer has this feature – the old ActiveX controls which used to be under the Developer Tab are gone. It used to be there, but now only on 32-bit Excel version.

What is the workaround?

We could make a drop down menu in a cell and limit its choices to only specific date values. Suppose you want to have a scheduling submission form where you want to limit people to only a particular period of time to pick from, like next 90 days or something. Here is how:

First we must create a lookup column where these date values will live. For simplicity I limited it only to dates for next 7 days.

First cell will need to auto populate with today’s date =TODAY().
The second cell will take the first cell and add 1 =G1+1, which will take today’s date and add 1, so tomorrow. The third cell will take G2+1 and so on and so forth. Each cell will take a value of the cell above it and add a 1.
This list will auto update every day, and the dates will always show the next 7 dates.

Want some customization to the date range?

=WORKDAY(TODAY()-1,ROW()) | only weekdays
=TODAY()-7 | Previous week
=TODAY()+30 | Starting 30 days from now

Next, we need to create a drop down menu.

Here we make that cell to be a Short Date format. Then we need to make it a drop down by following these steps:

• Go to Menu >> Data >> Data Tools >> Data Validation
• Set Allow field to List
• Set source to the range of cells where your dynamic dates are
• Be sure to check Ignore Blank
• Optionally check the bottom box to apply to all date cells
• Hit OK

It should look like this:

Now we have a drop down which limits the user to only enter today’s date or next 6 days. No manual entry, no typos and only the dates you want to allow them to pick. It is not perfect by any stretch but beats the hell out of manually entered dates. And believe me, if you see a 6/5/2026 you will be wondering is that 5th of June or 6th of May? Not everyone reads dates in MM/DD/YYYY format – especially native Europeans don’t.

There is a solution to get a true calendar picker into Excel using VBA, but it requires macro enablement and I’d not suggest you do this without coding experience. There are plenty of examples online. I’m not a fan of any of them

Need help?

Need help streamlining your processes or solving tricky business problems? I offer one-on-one consultations to get you unstuck fast. Book a free consultation with me today at goarcherdynamics.com.

Want more practical tips and workflow hacks? I publish them regularly on my blog — check it out and subscribe for updates: goarcherdynamics.com