Welcome to Excel Intuition, this is the Suzanne. This module looks at date functions. If you haven’t already done so, I recommend you pause the video and download your own copy of this companion workbook so you can follow along in your own workbook, and then you can work the challenge worksheets before they’re demonstrated in this module.
So the first thing I want to do is explain how Excel stores dates. One day is represented as a digit, as an integer. So basically the number 1, if i type a 1 into a cell, that initial one is in general format, but if i convert that into a short date, that 1 would represent the day January 1st, 1900, which is essentially the beginning of time according to Excel. So i’m going to go ahead and clear that contents because of already put that information into this illustration that i’ve created for you. We’ve got a timeline here that shows day 1, January 1st 1900 is stored as date serial number 1. And then a date in the future, which is recent in the present time, say the date of November 29th, 2022. That date is stored as serial number 44894.
So up here, the same number in general format is that serial number 44894, but in date format it looks like November 29th, 2022. Which is a 122.99 years from the beginning of time according to Excel.
So the main reason to explain this is so that you understand that you can do date arithmetic. You can calculate the years between two different dates by taking the later date minus the earlier date and then dividing that number by 365. So for example that’s how we got the number 122.99 to say the years between November 29th, 2022, and the beginning of time according to Excel. So let’s go look at some date functions.
We have TODAY that allows you to get the current date, formatted as a date. We have the YEAR function that allows you to extract the year from any given date. And the MONTH, it will allow you to extract the month, it would be a number between 1 and 12. And then the DAY for any given date would be a number between one and thirty one. The year can only be, the earliest year can be 1900, and then it goes up to 9999. Of course we have a long way to go to get there. You can build a date by putting together the year, the month, and the day, and Excel will figure out which serial number represents that date that you are specifying, and create that for you.
So we’ve got a worksheet here that has a number of examples using date type functions. We’ve got today’s date, January 18th, 2023. And you can see the function that is in this cell is up here in the formula window, it’s the TODAY function. If i double click in here you can see it in the cell, the TODAY function. And there’s a birthdate entered here, you can change this birthdate, and put any birthdate you wish in here. I’ve put in December 31st, 1937. So somebody with that birthdate. The YEAR function applied to the value in C7 results in the year 1937. The MONTH function applied to C7 results in the value 12 for December. The DAY function applied to C7, notice the DAY function has the serial number argument, and this date in C7, even though it’s formatted as a date, behind the scene it’s a serial number that represents that date, and that argument results in the day 31.
The current age in years estimate, you can get by subtracting today’s year, subtracting the birth year from today’s year, and that’s what we’ve done right here. We’ve said the year of today, which would be 2023, minus E7, which is the year of the birth year. That results in an estimate that that person is eighty six years old. Then we can calculate, we can ask Excel to tell us what the person’s birthday this year will be by using the DATE function and giving it the year of today’s date, which would be this year, 2023, and then the month that they were born in, and the day of the month that they were born in, and that results in December 31st, 2023 being the birthday this year.
We can compare dates in an IF function in a logical test, and ask the question has that person’s birthday passed already. The logical test we’re applying is, is the birthday this year in C9 greater than or equal to today’s date. And if it is true that it’s greater then the answer is no the birthday hasn’t passed because it’s a greater number than today’s date which means it’s in the future. If it was the same as today’s date we would also say no because it wouldn’t have passed yet if it was the same as today it would be today.
Now we can do some calculations to say what was the year of this person’s 21st birthday? How would we do that? We would take the year they were born and just add twenty one, and we see that E7, the year they were born plus 21. If we want to ask when would they reach 70.5 years old, that’s a question we often want to know because that’s the year some people have to start taking withdrawals from their IRA accounts. How do we get the date when a person’s reaching 70.5? We can use the DATE function to build a date. We’re going to add for the year argument, we’re taking the year the person’s born plus 70. And then to get the half a year, we take the month they were born G7, and add 6 to it, for 6 months is is the same as half a year. And then we used the same day they were born for the day argument to create the new date. We see the result, they will turn 70.5 on July 1st, 2008, they they did turn 70.5 on July 1st, 2008. The date they reached 72 years, we did that by just adding seventy two to the year they were born, and using the same month, and the same day of month.
To get a more accurate age for this person we’re actually going to subtract the day they were born from today’s date, and divide that by 365. So they’re actually 85.11 years old right. Not actually eighty six yet because their birthday is not till the end of the year. And then what’s the half birthday following their birthday this year? We get that just by adding six months to their birthday this year. We get the year of this year, the year of today’s date, the year of C6, and then we use the month of their birthday which is in G7 but we add 6 to it and then we use the same day as their birthday for that.
Let’s go to another… Okay so these are all examples of using YEAR, MONTH, DAY, and DATE, and TODAY. Now we’re going to go to a worksheet where you can challenge yourself and I recommend you pause the video and see if you can do these before you watch the demonstration.
This is a worksheet that includes the names of some of the world’s present and past leaders. And if you click on these different names you can go to some information about that person if you’d like to. So we’ve got birthdates for these different individuals, and we’re going to use functions to answer these different questions. We’re going to start by putting in a function for today’s date, which we know is the TODAY function. There is no argument in that. Now to get the birth year for this birthdate we’ll use the YEAR function and give the argument of the actual birthdate to the YEAR function. And we can use the same function for the entire column. To get the birth month same idea, the MONTH function applied to the birthdate. And the day the person was born, we asked for the day of the birthdate. And you might just want to take a look and make sure all the answers are consistent with what you expect.
Now we have these years, were according to the information about these leaders, the years they became a world leader. So here you’re asked to calculate the approximate age they became a world leader. And we’re just going to assume that we can do that by subtracting the year they were born from the year they became a world leader. So the year they became a world leader minus the birth year will tell you the approximate age. So we’ve got age ranges from the youngest person was thirty six when they became a world leader and the oldest was somebody who was seventy six, Nelson Mandela.
So we have two different death dates here, this might not be completely up to date but this is the information provided in this worksheet. So we’re going to calculate what was their approximate age at death, but we’re only going to do that if there’s a value in this column. So we’re going to start out by testing. We’re saying if ISBLANK this cell, then we’re not going to put an age in this column, we’re going have a null value. Otherwise we’re going to estimate that their age at death was the year of the death date minus the year of the birthdate, and let’s see if that looks like what we’re aiming for. So we just got numbers on the rows where we had a value for the death date, and this person died in 2022, they were born in 54 and it estimates that they were sixty eight at the time of their death.
Now we’re asking in this column, if alive what would their current age be? Here we can do a more accurate calculation. We can take today’s date, and from that subtract the birthdate, and then divide by 365. That first calculation will tell how many years they’ve lived, and this will tell what their current age would be if they were born on that date. And we can reduce our decimals. And actually what I want to do, I want to put an IF function around this because we don’t want to do this if they have already, if we already have the knowledge that they are no longer living. So we’re going to put: ISBLANK… if the death date is blank then we’re doing this calculation, otherwise we are not going to put a current age in here. So we have an IF function: IF ISBLANK I7, then we’ll calculate their age, otherwise we’ll leave this cell looking empty. Okay so what we did wrong, when we do this calculation using today’s date, we have to put an absolute reference on that cell if we want to copy that formula to every row in the column. And now we’ve got their current ages of these leaders.
Now to do the birthday this year we’re gonna use the DATE function. The year is going to be the year of today’s date. And here we’re going want to use that absolute reference again. And then the month is going to be the birth month of the person in that row, and then the day is going to be the day of the month they were born. And we’ll get the birthday for everybody, even those who are no longer living.
And then to get the half year birthday following the birthday this year, we’re basically going to do the same formula but add six months to it. So we’ve got the year of today again, and we’ve got the month of the person’s birth plus 6, and then we’ve got the day of their birth. So that’s all for that worksheet and we have one more worksheet here.
We’re going to do some date arithmetic with calculating when a product is projected to expire if it’s purchased on today’s date. So first we’re going to enter a formula for today’s date using the TODAY function. And then we’re going to get the year, and the month, and the day of today’s date, because we may need each of those in our calculations. So we’re getting the year of today, the month of today. Let’s see what what went wrong here. I typed over something. And the day of today.
So what we’ve got in this table, we’ve got a number of different food items, and then we’ve got projected expiration if purchased on today’s date. And the data in here was gathered from a website that recommends how long things are likely to last. So in some of the comments I’ve given you details to tell you that any given food item, the amount of time before it expires is not going to be given to you in more than one column. So it’s either going to be given in days, months, or years. But they’re not going to be cumulative. So you can presume that you could… if you have days then you aren’t going to also have months or years. So we want to calculate the day in the future using the values in the days, months, and years columns, based on today’s date.
So we can say the projected expiration, it’s going to be the date of this year plus the value in the year, and this month plus the value in months, and this day plus the value in days. So in this case today’s date is January 18th, and 21 days from now would be February 8th. And this is not working very well because we have to put absolute references on the values coming from these cells. So we’ve got C5 needs to be an absolute reference, D5 needs to be an absolute reference, and E5 needs to be an absolute reference. Now let’s try again. And let’s take a look. For the ones that are supposed to last a year we’ve got today’s date plus a year, so January 18th, 2024. There’s something projected to last six months we’ve got July 18th, 2023, three months we got April 18th, and so on.
So that’s the end of our introduction to date functions. Please feel free to send any questions, comments, or requests. Thank you very much!