Next, in this tutorial, I will introduce to you some real tips about Excel for office workers, or those who work in communes, wards, .. in general, you often work with applications. use Excel.
Perhaps you are not familiar with the cases where column separation is required day, month, year in three different columns, or move day, month, year of birth from number to letter …yes right?
So do you know how to make such requests? Okay, if you are facing such requests then this article will be the one for you. In addition, in this article, I will also show you how to get people on the list by age. And specifically, in this tutorial, I will do it “Take out the elderly according to regulations” .
In case you have a list of elderly men and women in the commune, you want to filter out how many are eligible to receive gifts or participate in a small program at the end of each year. for them to live happily and healthy every day.
Okay, and here we go to the detailed tutorial:
1/ Separation of day, month and year produces 3 different columns
+ Step 1: You create a table as shown below => columns C6
date of birth: 04-05-1980 for example, you put the cursor in the cell D6
(first)type =DAY(C6)
(2) => press ENTER
, then you perform the command copy operation with the cells below (3)
And here is the result (4)
+ Step 2: Similarly, the Month column you do the same => you put the cursor in the cell E6
(first) type =MONTH(C6)
(2) => press ENTER
=> then copy the formula down.
And this is the result of the numerical month column (4)
+ Step 3: Next you make the year column => put the cursor in the cell F6
(first) type =YEAR(C6)
(2) => press ENTER
then copy the formula down according to the arrow shape (3)
And here is the result (4)
2/ Convert date, month, year of birth from number to word
Read more: [Tuts] 2 easiest ways to change numbers to words in Excel
+ Step 1: You create a table as shown below:
You put the cursor in the cell E5
(first) enter the formula =“Date”&DAY(C5)& “month”&MONTH(C5)&”year”&YEAR(C5) (2) => press ENTER
then copy the formula down according to the arrow shape (3)
And here is the result (4)
3/ Take out the elderly according to regulations
+ Step 1: You create a table as shown below => put the cursor in the cell D8
(first) enter the formula =YEAR(TODAY())-YEAR(C8) (2) => press ENTER
to calculate age => Then copy the formula down according to the red arrow (3)
+ Step 2: You create one more BẢNG CHUẨN
(first) side, as shown below.
Then you choose Formula
(2) => choose Define Name
(3) => choose next Define Name
(4)
Next, at section Name
you to be REMOVE (5) and press OK
(6)
And here is the numerical result (7)
Read more articles:
Epilogue
Okay, so I just gave you a very detailed guide how to split the date column into 3 different columns. And also share with you the formula to filter people by age in Excel.
Hopefully with such tips will help you in your daily work, as well as students can refer to to gain a little more knowledge.
Good luck to you all, and don’t forget to visit this blog, the self-study section for Excel
CTV: Kieu Hoa Minh – techtipsnreview
Note: Was this article helpful to you? Don’t forget to rate the article, like and share it with your friends and family!
Source: How to split the date, month, and year columns into 3 different columns in Excel
– TechtipsnReview