Excel date picker: insert an excel date picker calendar in a cell
Vložit
- čas přidán 29. 08. 2024
- * NOTE: This video is for the 32 bit version of excel - if you have the 64 bit version, try the second method below.
** UPDATE: check out our second video in this series - try it out if this method doesn't work for you! • Excel date picker: ins... **
*UPDATE 2: if the above methods don't work, have you considered using Google Sheets? Check out this video: • Google Sheets Date Pic... *
This beginners tutorial looks at one method of inserting a date picker in excel. Using the developer tab, we show you how to insert a drop down calendar in excel (office 365) and this calendar acts as a date chooser in excel. Also learn how to set a specific date range and link your excel calendar picker to a cell so that you can use the date in formulas.
You can follow along with this tutorial from a blank workbook! Learn this great tip to add a drop down box with a pop-up calendar.
Learn Excel online for free: Our Excel for beginners tutorial will teach you all you need to know to take you from zero to Excel hero!
What Microsoft Excel tips and tricks do you want to learn? Let me know for future videos! I'll continue to develop tutorials covering Excel basics for beginners and will build up a catalogue of free Microsoft Excel course materials! :-)
Check out our other videos and playlists
If you're missing the "Microsoft Date and Time Picker Control", be aware that this is not available in the 64 bit version of Excel.
That is very true - as I forgot to say this in the video, I had added a pinned post stating that (this video was made quite a while ago and not on a newer 64-bit version). I have a second video on the options (add-ins) you can use with current 64-bit 365. However none of the date picker options are particularly good unfortunately.
That is why I cannot see it in the list.
The "Microsoft Date and Time Picker" control is no longer included in current 64-bit Office 365 Excel. The instructions in this video won't work.
Agreed - I had added a pinned post to that effect as this video was made quite a while ago and not on a newer 64-bit version. I have a second video on the options (add-ins) you can use with current 64-bit 365. However none of the date pickers are particularly good
As the options in Excel aren't great, I've done a video on alternative with google sheets. Might be worth checking out: czcams.com/video/Cx0GFaTXqdY/video.html
Why would Microsoft do that instead of making the feature as good as they could.
Everyone wants this obviously. the only people who don’t use this are those who don’t know how
Who knows why the dropdown isn't just standard on Date formats like in Google sheets - would be a great bit of functionality!
@Smitty-op4ld The more I use Excel the more I find out that they lack a lot of good stuff.
Google sheets has truly outdone them.
For those of you that can't find the SP4 option, it might be you have the 64 bit version of excel, which I've recently got myself and the SP4 version of the date picker doesn't work. If so, I've done a video on an alternative method of adding a date picker. Feel free to check it out! czcams.com/video/-lRRAIzMlC0/video.html
As the options in Excel aren't great, I've done a video on alternative with google sheets. Might be worth checking out: czcams.com/video/Cx0GFaTXqdY/video.html
Thank you for your time. What if I want to have the date picker visible in the entire column
I was wondering what you mean when you say visible in the entire column? Are you meaning multiple date pickers or just the view of the single one?
@@BitesizeExcel Oh thanks. Yes, I want date picker multiple cells in the date column
You can insert multiple date and time pickers - or copy the row with design mode turned on. You will probably have to manually update the linked cell, but I've done this before when I wanted more than one date picker in a worksheet.
@@BitesizeExcel Thanks. Will give it a try
As important as it is to have a calendar option handy for setting appointments, scheduling a meeting and travel dates, why has Microsoft not developed an option to insert the calendar into a cell to pop-up when formatting a date cell/column?
I really don't know.... I wish they do what Google sheets does and fave a mini calendar pop up when something is formatted as a date!
Great thanks, but how do I do it for Excel for Mac?
Thank for the comment. I'll see can I borrow a mac to explore the options. I also have an alternative video on using Google sheets, as the excel options are a bit rubbish.
THANKS! WORKED PERFECTLY.
You are welcome. Glad it worked for you!
Straight to point, thanks. Is there a way that we can have this to be within the cell itself, and not as on object like you showed?
Thanks for the comment and glad that you found it useful. ❤️
I've not found a way to add a calendar-style date picker (like in the example) directly into a cell - that is why I link the object to the specific cell I want, change the settings to 'Move and size with cells' and align the object to the edges of the cells by holding down 'Alt'.
There are some ways to get date drop down lists using data validation, but these usually involve having a date list somewhere else in your workbook. Still useful to know as an alternative. I can do a video on this method if it's of interest! 🙂
As the options in Excel aren't great, I've done a video on alternative with google sheets. Might be worth checking out: czcams.com/video/Cx0GFaTXqdY/video.html
Once I close the file and reopen it, the date picker does not work. Have to go back in design mode, move it and close design mode to get it working again.
It isn't a great option to be honest. Hoping to record a new video soon on another option in office 365.
Very good! Happy to have subscribed your channel !!!
Glad you found it useful and thanks for subscribing! 😄
How did you get the date picker as an option to select from the tools. I am not seeing in my tools
It might be the version of excel you have, as not everyone will see the option. I have two other videos on alternative date picker options (and possibly a 4th coming) - see the pinned comment at the top or the video description for the links to these videos! 🙂
Great tutorial as always! 👍
Thank you! Glad you enjoyed!
I have gotten a warning "This formula is missing a range reference or a defined name." There is the word EMBED that is highlighted gray in the input bar and it will not let me correct this. How can I resolve this?
You may have accidentally added a formula to a shape when it's been selected in design mode. Try pressing escape twice if it is still selected and you can do anything. Or maybe delete an re-add with any formula. Hope this helps
Hi - all works - but the calendar moves up and down as I scroll up and down the sheet (it is stuck in a position on the sheet, not hovering and constantly visible) so it basically disappears as I scroll down my sheet. How do I fix this so that the calendar stays in the same place on my screen instead of moving with the sheet?
This version has always been a bit buggy, but unfortunately it isn't available on the new version of office I am using - so can't test your error and whether it is possible to fix. I have a second drop-down list add-in video that looks at some of the add-ins that are available. However, overall I don't find any of the available options are particularly good and for that reason I tend not to use them. I'm exploring potential alternatives (e.g. VBA), but not quite ready to post a video.
You are legend mate!
Why thank you - glad you found it useful! :)
@@BitesizeExcel it’s ok keep it up!
Hi. Thank you for the video, it was really useful and everything worked perfectly well. I was wondering if there was a way to update the min and max date automatically. In my case, I set the maxdate to today and the mindate to one year ago, using the formula today in properties, but it didn't work. Is it possible to link it to a cell maybe?
Thank you and glad you found the video useful. Unfortunately my current version of excel doesn't support this method, but have you tried having the max and min date in cells and then putting the cell reference into the corresponding properties boxes?
@@BitesizeExcel Thank you for answering so quickly. I've already tried it, but unfortunatly it doesn't allow me to do so.
That's a shame. I will have a think and see if I come up with an alternative for you. To be honest, none of the date picker options in excel are good. If I needed one, I'd probably upload my workbook to Google Sheets. I have a video on that they I've now linked in the description (or this link: czcams.com/video/Cx0GFaTXqdY/video.html)
Awesome video.
Glad you found it helpful!
Hi, the value stored in the cell is stored as text and not recognized as date. How to solve this?
Unfortunatel don't have this version of excel anymore. Have you checked my other date picker video (linked in description). Using the mini date picker calendar stores the value as a date in the cell for me, so might be what you are looking for!
What if i need to put a calendar in 1000 cells? Or everytime a row is added to my table?
To be honest - all the options in Excel are complete rubbish. If I really needed a drop down calendar in every cell that was formatted as a date, I'd use Google sheets for that workbook. Check out this video: czcams.com/video/Cx0GFaTXqdY/video.html
can this type of data pocket be added to a PBI report instead of Excel?
I've not done a video on date picker in PowerBI - but check out this video by Guy in a Cube - shows a few solutions and might be what you are looking for! 😄
Thank you for an informative video. I just wonder, I have Excel 365, put i don't find "Microsoft date and time picker controle 6.0 (SP4)", even if I open "More controls". Do you know what I can do? Kind regards, Harald
Thanks for the comment. It might be that the ActiveX control isn't installed. See the following thread on the Microsoft forum - it was for Excel 2016, but you could try for 365. Might also be worth checking your add-ins (File>Options>Add-ins) and see are there any date add-ins that can be enabled).
Might be you have the 64 bit version of excel, which I've recently got myself and the SP4 version of the date picker doesn't work. If so, I've done a video on an alternative method of adding a date picker: czcams.com/video/-lRRAIzMlC0/video.html
It's been removed from Excel in Office365
@@chads-stuff It has indeed. I have a second video on another date picker method, but currently looking at possible alternatives. None of the current options are really any good...
As the options in Excel aren't great, I've done a video on alternative with google sheets. Might be worth checking out: czcams.com/video/Cx0GFaTXqdY/video.html
thx that was helpful
Glad it helped
Great stuff thank you
You are most welcome! 😄
Hi, you must say " this solution is valid for office 32 bit only, not for 64 bit"
Thank you for the comment and I had forgotten to say this in the video. I had signposted the video for the 64 bit version in the description and have updated to try and make it clearer.
how to incert date time picker for 300 cells? Do i have to do like this one by one?
The excel options are not good - you would have to enter individually. To be honest - I'd open that workbook in Google sheets to select my dates - check out this video: czcams.com/video/Cx0GFaTXqdY/video.html
my excel doesn't have Developer tab
The Developer tab isn't displayed by default, but you can add it to the ribbon. I think I cover this in a number of videos, but here are the steps:
On the File tab, go to Options > Customize Ribbon.
Under Customize the Ribbon and under Main Tabs, select the Developer check box.
Hi I can't find Microsoft Date n time......
Mine starts with Microsoft External item picker
Date pickers are a bit difficult in excel - as the version in this video is only available in some 32-bit versions of excel. I see that you have seen the second method for adding a date picker, but this is a third-party add in, so again may not be consistent across versions. Unfortunately Excel doesn't have it's own built-in method for adding one. Hopefully this will change in future as lots of people would love to have an easy way to add a date picker in!
Very good...
Thanks. Glad you enjoyed!
Superb 👍👍
Thanks 🤗
I don't see the MS Date and time picker control
Depends on the version of excel you have - check out my other videos in the pinned comment to see if any of the other methods are better for you!
Thank you so much
You are most welcome, glad you enjoyed! Let me know if there is anything else you would like to see.
Just one more basic feature that's nativly and conveniently built into Google Sheets that's missing from Excel. I'm being forced to use Excel for work and it feels like going back in time about 15 years. Excel is a great example of the harm caused by monopolies.
Yes - is a shame that some of this basic stuff isn't just included!
pleae update for the current excel version
I have another video for the newer version of excel and one for using google sheets as an alternative - see the pinned comment for the link!
Fell at the first hurdle - this is not in my list of more controls
There isn't really an easy way of doing a date picker in excel - but I've got three videos on the topic, so hopefully one might work for you. Try my latest, which is using Google sheets: czcams.com/video/Cx0GFaTXqdY/video.html
@@BitesizeExcel Thanks will try. It's weird how Excel does not include this most basic of features by default but that's MS for you. How they became number one is a complete mystery to me.
It does seem like a pretty basic feature that should be really easy to include - particularly as it is just standard in Google Sheets. Fingers crossed that it gets included in a future update.
I'm using 32bit.. I don't have this :(
I've got a couple of other date picker videos with other methods - they should be linked in the description. Hopefully one of those helps. To be honest - the date picker options in Excel are really rubbish!
Only works on 32bits...
That is correct - forgot to mention in the video but think I updated in the description. I have a second video for a 64 bit version of excel and, since the options in excel are rubbish, an alternative way of getting a date picker using Google Sheets - do a search for date picker on my channel and these should come up.
How do add a "date picker" for 200 rows in a column?
To be honest there isn't really a good method to do this in the current versions of excel - date picker options are poor. I have a second video on the options (add-ins) you can use with current 64-bit 365. However none of the date pickers are particularly good, particularly if you want to add multiple date pickers. You could probably insert multiple date and time pickers - or copy the row with design mode turned on. However, you will probably have to manually update the linked cell (I can't test this method as now have the newer version of excel).
As the options in Excel aren't great, I've done a video on alternative with google sheets. Might be worth checking out: czcams.com/video/Cx0GFaTXqdY/video.html
A1 👌👌
Glad you found it useful! :)