With the help of functions, you can easily split date in excel. In this article you will learn how to do it in an efficient way
Click here to download Start File
Same occasions require to divide the elements of a date: days, months and years. It is possible to do manually, however this path may be slow and inefficient. The objective of this tutorial is to demonstrate a professional method.
Firstly, it will be presented in 3 parts that represent 3 forms of parsing a date: as number, as text and text to number.
Figure 1
PART 1 – AS A NUMBER
The date will be converted to the corresponding number of the element. For example, February would become 2 and December would be 12.
Use the functions bellow, considering that the argument will be the cell with the date to be splitted.
Figure 2
In the case bellow, the function will be applied on the Column C, using as references, the dates on Column B, matching the formula with the corresponding component.
Figure 3
Figure 6
Figure 4
Figure 5
If there are several dates, it is necessary to set the formulas once. After the first time is done, you can enter the other dates in the next cells, on the same row, and drag the formulas to the right.
Figure 8
Figure 7
PART 2 – AS A TEXT
Here, the goal is to extract the date´s component as a text. The function converts the date into a text in a specific number format settled within the formula.
In this case the function is the same for all elements: days, months or years.
Function
Figure 9
The table below offers a support to structure the format of the text. For example, if the objective is to return the whole word, put “DDDD” for days or “MMMM” for months. There are only the 3 possibilities then: the number, the first 3 letters of the word or the whole word.
Figure 11
Figure 10
Now, observe the procedure that result part of the word.
Figure 12
Figure 15
Figure 13
Figure 14
In case of several dates, repeat the procedure of enter the others on the next cells and drag the formulas to the right.
Figure 16
PART 3 – TEXT TO A NUMBER
Firstly, it is important to be aware that dates are numbers to Excel. The primordial date is 1st of January 1900. Every day is the increment by one. For example: 19 th of April 2019 converted to number is 43574, which is the amount of days summed to the day 1.
Secondly, this function only works with text. Dates customized in any format such as: “March, 14, 2018”, will deliver “#Value”: the message of error.
Function:
Figure 18
Figure 17
To achieve an efficient result, structure a vertical list with the dates text
Select the range beside, in this case it is D15:D18.
In cell D15, enter the formula, using C15, which is first cell with date text as argument.
Figure 19
Apply the short cut to settle the formulas in the whole list, once. Notice that in some keyboard “Return” is “Enter”
Figure 20
Figure 21
To format the number as date, the most efficient path is to use other shortcut:
Figure 22
Figure 23
Figure 21
The final table:
If you learn better with a video, we recommend you watch our friends video on how to split date in excel
Thanks for your attention!
