Learn to Extract Information from a Date in Microsoft Excel

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)


Please wait...

Subscribe to ExcelMOOC posts

Want to be notified when our Excel article is published? Enter your email address and name below to know all new Excel features and cool tips