How to work with Date Picker in Excel

Date Picker is a dropdown calendar that helps enter dates in Excel. Just run the tool and click the date that you want to insert into the selected cell. With Date Picker, you can also change a date by adding or subtracting years, months, weeks, and days.

Video: How to use Date Picker

How to turn Date Picker on and off

  1. On the Ablebits Tools tab, in the Date & Time group, click Date Picker:
    Enable Date Picker in Excel.

    Date Picker is enabled, so now each time you select a cell with a date you'll see the Date Picker icon on the right. Besides, you can open the dropdown calendar by right-clicking a cell and choosing the Select Date from Calendar option.

  2. To turn Date Picker off, click Date Picker on the ribbon again.

How to insert a date into a cell

Right-click any cell and choose the Select Date from Calendar option from the context menu:
The Select Date from Calendar option

Tip. If you already have a cell with a date, you'll see the Date Picker icon next to it. Use this icon whenever you need to quickly replace the date in Excel.

The Date Picker icon

Here is the Date Picker pane:
The Date Picker pane

  1. Click the Date Calculator icon if you need to add or subtract dates.
  2. With the Two-month view icon, you can expand the Date Picker pane to two months. To get back to the one-month view, click the double arrow once again.
  3. Click the Today icon to switch to the current date in the calendar.
  4. The Up and Down icons will help you navigate between months.
  5. Use the Undo icon to cancel your choice and return the value that was in the selected cell previously.

Double-click the needed date or click it and press Enter to insert the date into the selected cell.

Tip. When you click any date in the calendar, you'll see it preset in the selected cell. The date will also be displayed at the top of the tool pane with a tip indicating the difference between the today's date and the selected one.
Tip. Click the month name to switch to the year view, and click the year to switch to the decade view:
The Date Picker year and decade views

How to calculate dates in Excel

Right-click the cell that contains the date you want to use for calculations and choose the Select Date from Calendar option from the context menu. Or click the Date Picker icon next to the selected cell:
Date Picker icon

Note. If an empty cell or a cell with a non-date value is selected, the current date will be used for calculations.

To open Date Calculator, click the Date Calculator icon in the upper-right corner of the Date Picker pane or press F4.
Date Calculator for Excel

You'll see the Date Calculator pane and its elements:
Add years in Excel.

  1. When you run Date Calculator, the addition (+) is chosen as the default operation. If you need to subtract dates, just click the minus sign (-). You can also use the corresponding buttons on your keyboard.
  2. The result of the calculation.
  3. The units you can add or subtract.
    Tip. To comfortably move between YEARS/MONTHS/WEEKS/DAYS, use the Left and Right arrow keys on your keyboard. You can also press:

    • The Y key for years
    • The M key for months
    • The W key for weeks
    • The D key for days
  4. The Backspace icon. Click it to cancel entering the last digit or press Backspace on your keyboard.
  5. The Clear icon. Click it to remove the last entered digit. Double-click this icon to remove the whole entry.
  6. The Show result in calendar icon. Use it to display the calculated date in the calendar.
  7. Click the Put result to cell icon to insert the result into the selected cell.

Use the number keys on your keyboard or click the digits on the Date Calculator pane to enter the number of years, months, weeks, or days you want to add or subtract.

Click the plus sign (+) to add or the minus sign (-) to subtract the entered number of years, months, weeks, or days.

Click the equal sign (=) or press Enter on your keyboard, and the result of the calculations will be displayed on the pane:
Subtract dates in Excel.

Tip. Click the equal sign (=) to perform calculations with the resulting date.
Tip. Double-click C on the pane or press the C key on your keyboard to remove all the digits.
Tip. Press the F6 key or click the Show result in calendar icon and you will get the date selected in the calendar.

Click the Put result to cell icon to insert the result into the initially selected cell.

A quick way to calculate a date

  1. Select a cell with the date you want to use for calculations and click the Date Picker icon next to it.
  2. Press F4 to open the Date Calculator pane.
  3. Perform the necessary calculations.
  4. When you see the desired result on the Date Calculator pane, press Ctrl+Enter and get the resulting date pasted into the cell that was initially selected.

Responses

Naseeb Peerbux says:
May 11, 2021 at 2:37 am

Is there a shortcut where I can change the Date Picker by one or few days. E.g I selected today in my date picker and later realised that it should be yesterday. With a short cut like -ve sign, the date could have reduce by one day. Actually many software has this option. Thanks

Hello Naseeb,

Thank you for your question. Unfortunately, there is no shortcut that could be applied to Date Picker. However, there is Date Calculator embedded into the add-in. With its help, you can quickly add or subtract days, weeks, months, or years to/from the selected date. Please see how to calculate dates with the add-in in this part of the manual above.

If you have any other questions, please do not hesitate to ask.

Can you have two date pickers on one sheet? I am struggling making them both work.

Hi, Svetlana,
Thank you for your comment and for using Date Picker. Unfortunately, there is no way to have two Date Pickers on one sheet, we didn’t even think of such a use case. Could you please explain in more detail why do you need two Date Pickers on your worksheet? We will think of a way to help you.
Thank you.

Albert O'Halloran says:
November 17, 2018 at 11:26 am

Can you include week numbers i.e. week 46, week 47 etc, in the pop up calendar? I just discovered albebits and I think it is great. It has made my job a lot easier. Thank you

Hi, Albert,
Thank you so much for your comment, it is very inspiring for us to know that our work really helps!

And thank you for the suggestion on showing week numbers in the calendar. I have passed it to our developers and in the coming updates we will implement this feature.

Thank you.

Is it possible to embed the date picker in the cell so that users without the add-in will only need to pick a date from the calendar? I am creating a form for our company users and I want to have a calendar for them to pick a date instead of keying it in.
Thank you.

Hi Henry,
Thank you for using Date Picker and for an interesting question. Unfortunately, it is impossible to embed Date Picker in the cell and make it appear on the machines that do not have the add-in installed. I believe that you may find a solution of your task here, maybe one of the described ways to create a calendar in Excel will work for you.
Thank you.

Is there a way thet the excels "Worksheet_Change(ByVal Target As Range)" can recognise that the date picker has changed the value of the cell in that range

Is there a way to have the cell default to the calendar? i.e. when you click the cell, the calendar to select the date automatically pops up?

Hi Jeff,
Thank you for using our product.
In the current Ultimate Suite version, there is no such an option to make the calendar pop up automatically. I will pass your question to our developers and maybe this feature will be introduced in the coming release :)

Is there a way to customize how the date is initially displayed in excel without having to change after the date is picked? Preference is dd-mmm-yyyy (12-Jul-2018).
Thanks, J

Hi Johanna,
Thank you for your question! Now you can’t do that, but in our coming update of Ultimate Suite for Excel, we will fix it. Date Picker will use the format of the cell initially selected. So first you will have to format your cell as Date 12-Jul-2018, and only then pick a date in the Date Picker calendar.
Thank you for helping us become better!

How do I apply the date picker to an entire row? I can only get it to apply to one cell at a time. I need to send form out and need about 300 people to fill it out and need them to be able to select a date in an entire row.

Hi David,
Unfortunately, there is no possibility to apply Date Picker to an entire row. The tool works for only one cell at a time. We are sorry it can't help you with your task. We will look into the possibility to implement this feature in our future releases.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.