Excel Formula to Calculate First Day Of Month From Date - Episode 942
Microsoft Excel Tutorial: Excel Formula First Day Of Month From Date.
Welcome back to the MrExcel netcast! In today's video, we'll be tackling a question from Rod about finding the first of the next month in a data set. Rod has a formula that returns the first date in the data set, but he wants to find a way to automatically get the first of the next month, and the next month after that, and so on. Let's see how we can solve this problem together.
At first, Rod tried adding +30.5 to the first date and dragging it across the worksheet. While this worked for most months, it caused issues when the first date was January 1st, as it would give the same month twice. So, we'll delete that and try a different approach using the DATE function. This function requires three things: a year, a month, and a day. We'll use the year and month from the cell to the left, and for the day, we'll hard-code it to always be 1. Then, we'll copy the formula across the worksheet and voila! We have the first of the next month for each date in the data set.
One great thing about using the DATE function is that it automatically updates if the date in the data set changes. So, if Rod gets data for 12/1/2008, the formula will still give the first of the next month for each date. This is a safe and reliable way to get the desired result. However, if you don't need a formula and just want to get the months going across, you can simply right-click the fill handle and choose "Fill Months" from the menu. This will give you the first of each month without the need for a formula.
I want to emphasize that this solution is based on the assumption that Rod needs a formula. If you don't need a formula, simply right-click and drag the fill handle to get the desired result. This is a handy trick to know for future use. Thank you, Rod, for sending in your question and thank you for watching this netcast from MrExcel. We'll see you next time for more helpful tips and tricks. Don't forget to like and subscribe for more Excel tutorials. See you soon!
Buy Bill Jelen's latest Excel book:
You can help my channel by clicking Like or commenting below:
Table of Contents:
(00:00) Formula for the First of a Particular Month in Excel
(00:22) Formula returning first date in data set
(00:32) Need formulas for first of each month
(00:42) Trying a different approach
(01:14) Using the DATE function
(01:52) Safe and automatic solution
(02:13) Formula vs. non-formula solution
(02:25) Clicking Like really helps the algorithm
This video answers these common search terms:
how to use the DATE function in Excel
excel formula first day of month from date
calculate beginning of month excel
get first day of month from date formula
excel formula start of month
calculate first day of month from date in excel
excel formula for month start date
get beginning of month from date excel
excel formula to find first day of month
calculate first day of the month excel
excel formula to extract first day of month
Join the MrExcel Message Board discussion about this video at
Rod has a formula in column B that calculates the earliest month in a data set. He then wants formulas in C, D, E, and so on that show the first of the next month. Although you can right-click the fill handle to do this, Episode 942 will show you how to solve this using the DATE function.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Смотрите видео Excel Formula to Calculate First Day Of Month From Date - Episode 942 онлайн, длительностью 03 минут 16 секунд в хорошем sd качестве, которое загружено на канал MrExcel.com 03 Февраль 2009. Делитесь ссылкой на видео в социальных сетях, чтобы ваши подписчики и друзья так же посмотрели это видео. Данный видеоклип посмотрели 2 тысяч раз и оно понравилось 16 посетителям.
