Vlookup Date Picker for Excel - Dynamic Date Generation

This is part 2 of the vlookup date picker video tutorial.

This part shows you how to create the dates for the drop down menus and how to make them dynamic so that they always show the correct number of days based on the year and month selection.

The days of each month will be accurate based on the year and month selected, even for February and leap years!

This tutorial combines: Vlookups, data validation, and complex custom formulas.

Excel Courses:

Chapters:
0:00 Intro & What it Does (Recap)
1:45 Drop Down List - Years
5:45 Drop Down List - Months
6:49 Drop Down List - Days

Additional Resources

#msexcel #howto #tutorial

[wayneedmondson]

Awesome! A great exercise and tool. Made my own version with FILTER and some tricks to have the validation lists show only the applicable years in the data, then the months having data in the year selected, and then the days having data in the year and month selected. So, if say 2019 has only Jan, Apr and Sep with data, then when selecting 2019 as the year, the month validation list presents only Jan, Apr and Sep Same logic for the day list.. presents only the days that have data, given the year and month selected. It required a few formula tricks inspired by your version. Thanks for sharing your example and providing the inspiration to create and learn :)) Thumbs up!!

[Excelambda]

Great video !!✌😉 Data validation accepts dynamic arrays references (like I3# or J3#) so for months we can use
in I3: =TEXT("1-"&SEQUENCE(12), "mmmm") (for Jan; Feb ..format we can use "mmm")
and for days
in J3: =SEQUENCE(DAY(EOMONTH(D3&"-"&C3, 0))) no other columns real estate needed. ✌😉

[micheltw]

I wish I could be as fluent as you in EXCEL, still amazed everytime I watch one if your tutorial. Hats off Sir👍

[HieuPham-utei]

Thank you so so so muchhh! somehow I found this video and it helped me a lot for an extra point.

[darrylmorgan]

Really Impressive, Great Explanations Of The Formulas...Thank You Sir :)

[joguihuazhou]

Using Sequence to return days is a nice alternative of Indirect.

[mimieghani]

hi, hope you are doing great. when I do this tutorial I have this error #VALUE! I'm using microsoft office 2019

[kiran]

How to delete (-30days) in excel using VBA code

[kathyroberts]

I think I will just leave Excel in the hands of the experts.... sigh