I love the DATE function. While some Excel functions are rather picky, the DATE function can handle just about anything that you throw at it. Want to know the 35th day of January? No problem. Want to know the 17th month of 2008? Easy. Want to know why this matters? Well, watch Episode 540 for a cool trick with the DATE function.
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.
In yesterday's podcast we talked about using the date function to solve George's when do I send customer birthday cards question, and it just reminded me of one of my favorite things about the date function of all time.
The date function as we talked about yesterday is basically a function that takes three variables it takes a year so for example 2007, a month, and then a day, so if we wanted to call out the let's say the 29th of June we put in 2007/6/29, and it will give us that date simple enough but the thing that's amazing about the date function is that it can handle really strange things for example if we would ask for the thirty fifth day of June, it is smart enough to figure out that we're probably talking about the fifth of July, it's also possible to ask for maybe like the seventeenth month of 2007 and the fifth day which will give us probably a date in May sure enough May 5th 2008, so the date function is really good at handling things that don't seem to make a lot of sense and one of my favorite examples of this is a question that came up a long time ago at the MrExcel message board, someone said; hey I have a column of date and I need to come up with a formula that will show me the last day of the month the last day of the month, well that was pretty difficult we started getting into kind of a discussion about well if its January, March, May, we're going to have 31 days if it's April or June we're going to have 30 days, and if it's February we're going to have to look at the year maybe it's 29 maybe it's 28 and we were trying to build this big huge If statement and Aladdin came along and said; hey you guys have it completely wrong here's what we're going to do we're going to use the date function and we're going to go to the first of next month, so which year we want we want the year that we have in column B which month we want we want one plus the month that we have in column B, and then which data we want we want the first all right, so that always gives me the first day of the next month and then Aladdin says; once you have that date it's simple just subtract one from it and Excel will do the date math to figure out the last day of the month, sure enough it works in leap years no big If statement required at all one of my favorite formulas this was a great example of where the first approach building a big huge If statement seemed the way that everyone latched on to, and then someone walked in the room and said now try this and everyone said oh that is far superior to what we were trying.
The date function one of my favorite functions in Excel.
Thanks for stopping by, we’ll see you next time for another netcast from MrExcel.
In yesterday's podcast we talked about using the date function to solve George's when do I send customer birthday cards question, and it just reminded me of one of my favorite things about the date function of all time.
The date function as we talked about yesterday is basically a function that takes three variables it takes a year so for example 2007, a month, and then a day, so if we wanted to call out the let's say the 29th of June we put in 2007/6/29, and it will give us that date simple enough but the thing that's amazing about the date function is that it can handle really strange things for example if we would ask for the thirty fifth day of June, it is smart enough to figure out that we're probably talking about the fifth of July, it's also possible to ask for maybe like the seventeenth month of 2007 and the fifth day which will give us probably a date in May sure enough May 5th 2008, so the date function is really good at handling things that don't seem to make a lot of sense and one of my favorite examples of this is a question that came up a long time ago at the MrExcel message board, someone said; hey I have a column of date and I need to come up with a formula that will show me the last day of the month the last day of the month, well that was pretty difficult we started getting into kind of a discussion about well if its January, March, May, we're going to have 31 days if it's April or June we're going to have 30 days, and if it's February we're going to have to look at the year maybe it's 29 maybe it's 28 and we were trying to build this big huge If statement and Aladdin came along and said; hey you guys have it completely wrong here's what we're going to do we're going to use the date function and we're going to go to the first of next month, so which year we want we want the year that we have in column B which month we want we want one plus the month that we have in column B, and then which data we want we want the first all right, so that always gives me the first day of the next month and then Aladdin says; once you have that date it's simple just subtract one from it and Excel will do the date math to figure out the last day of the month, sure enough it works in leap years no big If statement required at all one of my favorite formulas this was a great example of where the first approach building a big huge If statement seemed the way that everyone latched on to, and then someone walked in the room and said now try this and everyone said oh that is far superior to what we were trying.
The date function one of my favorite functions in Excel.
Thanks for stopping by, we’ll see you next time for another netcast from MrExcel.
