Not sure which Excel course you should do? Try out our free Which Excel Course Should you do" questionnaire. 20 questions and you will know exactly what course you should do!
How to use the DATE, DAY, MONTH and YEAR Functions in Excel
The DAY, DATE, MONTH and YEAR functions in Excel identify the components of a date that you may be interested in and converts it into a date that Excel can read.
In this example we have a couple of employees and their dates of birth because their bonuses are paid out on the 25th of their month of birth. So what we would like to do is get the exact date of birthday in the current year. What we need to do is break these birth dates into a DAY, MONTH, YEAR so that we can get rid of the year and create a new date.
The first thing to work on is the day. In order to extract the day there is a function in Excel called DAY. So if we go to the function wizard, go to the DATE and TIME category you will see that there is a DAY function, and we say OK. All it asks is tell me where the serial number is or the date. We will just point it here. When we say OK you will see it takes out the 18th as the day.
The next thing we want to extract is the MONTH of the birthday. So again, we activate the Function Wizard, go into the DATE and TIME categories, and there is a MONTH function here and we say OK. Again it asks us for the date we are looking at so we point it here and when we say OK, you will see it extracts the month of this persons birthday.
In this example we don’t really need the year, but we are going to extract it anyway. So we can click on a cell, activate the function wizard, and go to the YEAR. It also asks for the date cell, if we click in here, and say OK we have the year of the birth date, so we now have extracted the Day, Month and Year of the birth date individually.
We now want to turn this information into something that Excel can read and for the day we want to use the 25th as our payout date, the month will be the month of the persons birthday, and the year will just be the current year. There is a function in Excel called DATE which we can access through the function wizard, here, and now you just need to point to tell it where it must look for the year, and in this case we know we want the current year. It says where must I look for the month, and we know we want the persons birth date month which is over here. And the day, we know the payout is going to be on the 25th so we click it here. When we say OK, you will see it generates a date which is the bonus payment day, in the month of his birthday, in the year of his birthday. We can now easily copy all this information, take it all the way across, and you will see that immediately you will know the actual date, of when bonuses will be paid out, based on the birthday’s of the various employees.
Please fill in your details below to get regular Excel tips and tricks and a free Quick Reference Download!
We recently held our first Meetup group for South Africa. If you don’t know what Meetup is, it is a site to arrange ‘meetups’ for like minded people. In this case it is for everyone who loves all things Excel and … Continue reading
Everyone loves that way Excel can second guess what you mean when you type something into a cell and it (correctly) changes the format. Type 2 Sept and it will automatically format it as a date. But there are some … Continue reading
I actually told Gavin this morning that I want to send you an e-mail. It was excellent. I have personally asked most of the people who were there what they thought about it, and everybody was really positive and confirmed that they learned a lot.– Melco Conveyor Equipment (Pty) Ltd