If you work with a database and have someone’s birth date but you need to create a column that shows only the year when a person was born, how could you do this?
You could have a date in a cell with the whole date (year, month, and day) and the time of the day as well, but you need to extract only the date from the cell without the time.
Another case could be that you have a certain text in a cell with a date at the beginning or the end of the sentence.
How to extract this desired data? Read on to find out.
Extract the year from the date
If you happen to have an Excel cell containing the year, month, and day, and you want to extract only a year, then you can insert the formula in another cell:
|
1 |
=YEAR(A1) |
Let’s take a look at the picture below:
In this example, the original cell is cell A1. In cell B1 we insert a formula =YEAR (A1) and we get only the year in the cell.
Extract the month from the date
If you want to do the same thing, but only to get the month extracted from the date, the formula for the same example (picture below) is:
|
1 |
=MONTH(A2) |
Now, keep in mind, that Excel does not extract the name of the month with this formula. If you have the date in the original cell (cell A3 in the example) in the following format: 21-January-2021, Excel will still show the number “1” in cell B3, as shown below:
The reason for this is that Excel stores dates as numbers and then formats them as dates. You can read more about this subject in the article on how Excel stores date and time.
Extract the day from the date
If you want to extract the specific day from the whole date, you can use the formula:
|
1 |
=DAY(A4) |
Like in the example below:
Extract only the date when you have the date and time
If you have a cell with a date and time, but you need only the date and not the time part, you can use the INT function.
In the example below (B5) this function is used.
Just make sure to format a date (right-click >> Format Cells …) in the cell with a formula so that this one does not include a time. Otherwise, you will always end up with 12:00 AM time (12.1.21 12:00 AM in our example).
The INT function rounds a number down to the nearest integer. This formula is primarily used for numbers, but it can be used for dates as well.
Extract the date from the beginning of the text
If you find yourself in a situation where you have a certain text in a cell, with the date at the beginning of it, there is a formula you can use to extract the date.
|
1 |
=LEFT(A6,10) |
Extract the date from the end of the text
If you have your date at the end of the text, you can use the reverse formula: RIGHT.
This formula works the same way as LEFT. The only difference is that it returns the text starting from the right side (see the example below).
Enter the following formula into cell B7:
|
1 |
=RIGHT(A7,10) |
This formula returns the same result as the previous one.
Convert extracted text to date
As you can see, the last two cells are, by default, aligned to the left. This means that they are not numbers or dates, but normal text.
To convert it into dates, you have to use the DATE function. This is what the formula looks like:
|
1 |
=DATE(RIGHT(B7,4),MID(B7,4,2),LEFT(B7,2)) |
Let’s take a look at this formula. There are two functions from the previous examples: RIGHT and LEFT. But there is also a new one: MID.
This function returns a substring from the middle of the text, given the starting position and length.
The result looks like this:
If you want this result to look the same as the text from cell B7, you have to change the date formatting.