Learn to Extract Information from a Date in Microsoft Excel

 

 

Using dates in Excel is very common and probably more than once you need to extract information from a specific date as the day, the month or the year. TEXT function offers an alternative to obtain such information.

 

 

Show the month of a date
 
Suppose the next column of date, the first task will be to obtain the month of each and present it as a text string. The function used in column B is: =TEXT(A2, "m")

 
Extract Month Information from a Date – Single Number

 
Indeed I get the month number each date. Now see what happens if I use the same function but in the second argument put two letters “m” as follows: =TEXT(A2, "mm")

 
Extract Month Information from a Date – Two Numbers

 
The difference from the previous result is that now each month are displayed with two digits putting a leading zero if required. In the next picture you can see what happens if in the second parameter of the function TEXT put three, four and five letters “m”.

 
Extract Month Information from a Date – Other Formats

 
Show the day of a date
 
Similarly as in the previous example we can obtain information of the day. Notice how using the TEXT function and specifying the second parameter as the letter “d” we may obtain information about the day.

 
Extract Day Information from a Date

 
TEXT function helps us to specifically identify the name of the day and thus can make an analysis of information according to specific days of the week.
 
Show the year of a date
 
Finally, for the year there are only two possible alternatives that are getting the year with two digits or four-digit format.

 
Extract Year Information from a Date
 
Working with dates is very important and aware of the possibility offered by the TEXT function to work with this type of data will be very useful. (Source: Excel Total)