Splitting a Date: Number or Text?

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:

 

Thanks for your attention!

Figure 24

Additional Information:

Count Words: 488

# of Steps: 11

 

Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>