Excel Date Delight: Fill 1st & 15th - Episode 2162

3,504 views · Published 25 October 2017 · 1:38 · Indexed 16 November 2026

Channel: MrExcel.com · 2017 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to fill the first and 15th of each month in Excel.

Welcome to another episode of MrExcel's podcast, where we dive into all things Excel. In today's episode, we will be discussing a simple yet useful trick for filling the 1st and 15th of every month. This question came up during one of my seminars in Raleigh and I knew I had to share it with all of you.

So, how do we fill the 1st and 15th without having to manually enter each date? Well, the first instinct might be to drag down the dates, but that will also include other dates like the 29th, 12th, and 26th. Not exactly what we want. Instead, select both the 1st and 15th dates, right-click and drag down to row 25. When you let go, a new box will pop up with the option to "Fill Months". Click on that and voila! The 1st and 15th dates will be filled for the entire year.

Now, you might be thinking, what about the 15th and the last day of the month? Don't worry, the same trick applies. Right-click and drag down to row 25, and choose "Fill Months". The only difference is that Excel will automatically adjust for the last day of the month, whether it's 28th, 30th, or 31st. This handy trick was actually taught to me by Bob Umlas and it has saved me so much time and effort.

If you're looking for more Excel tips and tricks, be sure to check out my book "Power Excel with MrExcel". It's filled with over 600 solutions to common Excel problems, including quick and easy tricks like the one we just discussed. So, whether you're a beginner or an advanced user, there's something for everyone in this book.

That's all for today's episode. I hope you found this tip helpful and will use it to save time and increase efficiency in your Excel work. Don't forget to subscribe to my channel for more Excel tutorials and tips. Thanks for watching and I'll see you in the next episode of MrExcel's podcast.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

#excel
#microsoftexcel
#exceltips
#exceltricks
#excelhacks
#exceldates
#excelfillhandle

This video answers these common search terms:
excel fill 1st and 15th of the month
populate cells on 1st and 15th in excel
automatically enter data on the 1st and 15th in excel
excel formula for 1st and 15th of each month
filling cells with specific dates in excel
excel date automation for 1st and 15th
excel fill 15th and end of the month
populate cells on 15th and last date of month in excel
excel fill 15th and last of the month
excel date automation for 15th and last of month

Table of Contents:
(00:00) Filling the 1st and 15th of every month in Excel
(00:19) Selecting and filling the dates
(00:35) Filling the 15th and the last of each month
(01:03) Thanks to Bob Umlas
(01:19) Clicking Like really helps the algorithm

Or, need to fill the 15th and the last of each month
Enter the first 2 dates. Select both dates.
Right-drag the fill handle
When you finish dragging, choose Fill Months

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153987/

More from this channel