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!
Popular Which Excel course should YOU do- updated to make the report more easy to find Training Dates Training Course Dates– Durban (Kingsmead) and Cape Town- See below Training Course Dates JHB/ PTA Tips and Tricks Copy a cell to … Continue reading
In our courses we often see delegates trying to copy cells to the left and making an incorrect assumption on which part of the cell to click when using the mouse. So in order to copy a cell to the … Continue reading
Your (AuditExcel) Advance Excel and Financial Modeling course is one of the best courses available on the market, it is well structured, relevant and insightful. I will recommend it to anyone who would like to improve his/her Excel and/or Financial Modeling skills.– Namibia