Erik points out that the best way to find the last day of this month is to ask for the zeroth day of next month. Episode 559 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
Today I have a note from Eric; Eric was talking about episode 540 where I used the date function to find the last day of the current month.
Let's take a quick look at the date function, basically the date function says we need to specify a year, a month in a day so I would ask for the year 2007, the month of 7, and the day of 27, I would get the data this podcast July 27, so let me format that so we can see what day of the week it is we'll see that that is indeed a Friday, and I'll copy this formula down to some other rows just to see how this exactly works, so if we ask for the first day of August 2007, month 8, day 1, it gives us Wednesday, August first.
But the date function is so versatile it can handle dates that don't actually exist if we would ask for the 0th day of August it actually gives us the last day of July or if we need to find something a week before the last day of July we could use the negative sixth day of August, it's also interesting that it works as well we can ask for it for example the 15th month and the 45th day and it'll figure out that that is April14th of next year so the date function is very versatile, back in episode 540 let us try to figure out the last day of the month I used a function where I said; equal date and we use the year of our date and then the month of our date plus one and then at that point I said we want to ask for the first day, close the function and subtract one, well instead the better way to go is just to put in that we want the 0th day =DATE(YEAR(C485),MONTH(C485)+1,0), and sure enough that will convert May 7th to May 31th, and you don't have to worry about how many days are in the month whether it's 28 in February or 29 it'll work all the way down so an improvement on episode 540, I want to thank Eric, I'll be sending out Eric as well as everyone else is sending a tip this week one of my Excel master pins as a thank you.
Thanks for stopping by; we'll see you next week for another netcast from MrExcel.
Today I have a note from Eric; Eric was talking about episode 540 where I used the date function to find the last day of the current month.
Let's take a quick look at the date function, basically the date function says we need to specify a year, a month in a day so I would ask for the year 2007, the month of 7, and the day of 27, I would get the data this podcast July 27, so let me format that so we can see what day of the week it is we'll see that that is indeed a Friday, and I'll copy this formula down to some other rows just to see how this exactly works, so if we ask for the first day of August 2007, month 8, day 1, it gives us Wednesday, August first.
But the date function is so versatile it can handle dates that don't actually exist if we would ask for the 0th day of August it actually gives us the last day of July or if we need to find something a week before the last day of July we could use the negative sixth day of August, it's also interesting that it works as well we can ask for it for example the 15th month and the 45th day and it'll figure out that that is April14th of next year so the date function is very versatile, back in episode 540 let us try to figure out the last day of the month I used a function where I said; equal date and we use the year of our date and then the month of our date plus one and then at that point I said we want to ask for the first day, close the function and subtract one, well instead the better way to go is just to put in that we want the 0th day =DATE(YEAR(C485),MONTH(C485)+1,0), and sure enough that will convert May 7th to May 31th, and you don't have to worry about how many days are in the month whether it's 28 in February or 29 it'll work all the way down so an improvement on episode 540, I want to thank Eric, I'll be sending out Eric as well as everyone else is sending a tip this week one of my Excel master pins as a thank you.
Thanks for stopping by; we'll see you next week for another netcast from MrExcel.
