Not sure which of these videos to watch? Try out our 'Which Excel Formula to Use' App to get guidance. NOTE: All these free Excel video tutorials are streamed via YouTube. If your network does not allow YouTube videos, you can access the video via your smartphone or from a different network.
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.
Accessing the task manager from excel A nice shortcut from @TomUrtis shows a quick way to get access the Task Manager from Excel. Normally trying to access the Task Manager is a bad sign in Excel, and in fact you can’t do anything … Continue reading
Excel Quiz Answers Answers for the quiz on our 11 Dec 2014 Newsletter. To subscribe to the newsletter visit the subscribe page. Excel Questions 1) The biggest risk with linking spreadsheets together is a. Rows and columns are added/ deleted when … Continue reading
“Data accuracy and consistency has improved dramatically across Absa Capital, so much so that it has become compulsory within certain areas for all staff to complete Adrian’s Data Analyses Spreadsheet Techniques course.”– Absa Capital