Buzz
When working with Excel, there are times when you need to manipulate date data. Splitting date, month, and year into separate columns in Excel is a common task that many people are interested in. There are numerous methods available to achieve this task. Here, we present some of the simplest and quickest techniques to split date, month, and year in Excel.
I. Using the DAY, MONTH, and YEAR Functions
A simple and quick method to split dates in Excel is to use the DAY, MONTH, and YEAR functions. First, you need to understand these three functions to effectively split date, month, and year in Excel.
1. The DAY() Function
The DAY function is used to extract the day from a date value.
Syntax:
|
=DAY(seri_number) |
Where: serial_number is the required argument, representing the date from which to extract the day.
2. The MONTH() Function
The MONTH function is used to extract the month from a date value.
Syntax:
|
=MONTH(seri_number) |
Where: serial_number is the required argument, representing the date from which to extract the month.
3. The YEAR() Function
The YEAR function is used to extract the year from a date value.
Syntax:
|
=YEAR(seri_number) |
Where: serial_number is the required argument, representing the date from which to extract the year.
4. Splitting Dates in Excel Using DAY, MONTH, YEAR
Example: Splitting dates in the date of birth column into separate columns.
Perform date splitting in the example as follows:
Step 1: In the first cell of the Date column, enter the formula =DAY(D2) and press Enter.
Excel will immediately extract the day from the date of birth cell.
Step 2: Copy the DAY function formula down the remaining rows.
Step 3: After splitting the day, proceed to split the month. In the first cell of the Month column, enter the formula =MONTH(D2) and press Enter to execute the function.
Now the month has been separated from the date of birth.
Step 4: Copy the MONTH function formula down the remaining rows.
Step 5: Splitting the year, in the first cell of the Year column, enter the formula =YEAR(D2) and press Enter.
Now you have split the first year in the Birth Year column.
Step 6: Copy the YEAR function formula down the remaining rows to swiftly split dates, months, and years in Excel.
II. Utilizing the TEXT Function
1. The TEXT() Function
The TEXT function, known for converting numbers to text, also serves as a time function in Excel. It calculates the time difference in hours, minutes, seconds, and returns the result as text data, which you can then format as a number or time.
Syntax of the TEXT function to split date, month, and year in Excel:
|
=TEXT(value, format_text) |
In which:
- value represents the date to be split
- format_text for day splitting is “d”, for month splitting is “m”, for year splitting is “yyyy”. If you want to add a leading zero to single-digit months or days, you set format_text as “dd” for day splitting, “mm” for month splitting.
2. Splitting Date, Month, and Year in Excel using the TEXT function
Perform date splitting with the example above using the TEXT function.
Step 1: Splitting the Date
In the first cell of the Date column, enter the formula =TEXT(D2,”dd”) and press Enter.
Now you have successfully split the date into the Date column separately. You can adjust or change the format to any desired form.
You just need to copy the TEXT formula down the rows below to quickly split all dates in the column.
Step 2: Splitting the Month
In the first cell of the Month column, enter the formula =TEXT(D2,”mm”) and press Enter to execute the function.
Now you have successfully split the month into the Month column.
Copy the TEXT formula for month splitting down the rows below to apply the function.
Step 3: Splitting the Year
In the first cell of the Year column, enter the formula =TEXT(D2,”yyyy”) and press Enter.
Immediately the year in the first cell of the Birth Year column will be split.
Simply copy the TEXT formula down the rows below to quickly split all years in the Birth Year column.
Note: When using the DAY, MONTH, YEAR functions, the extracted dates will be formatted as numbers. However, if you use the TEXT function, the extracted dates will be in General format. If you need to perform calculations, you should format them back to numbers.
So, these are the two simplest and fastest ways to split date, month, and year in Excel. The support of time functions will help you quickly separate dates, months, and years in Excel according to your requirements. Remember to save these simple methods for splitting date, month, and year in Excel for later use. Thank you for your interest and for following this article.
