8 Dec 2013

Extract day, month or year in Excel

Many a times, we go though dates given in format DD/MM/YYY in Excel, and struggle to extract out the day, date, month or year. Well fret no more, there is a simple solution / formula that will end the woes.

Suppose we have a date as 1/21/13 in cell A1, below are the possible data formats that can be extracted

A) Extract Day
1) Input this formula on a separate cell =TEXT(A1,"ddd") [Result: Mon]
2) If you wish to have a full length day, you can use 8 letters instead of 3 like this
=TEXT(A1,"dddddddd"). [ Result: Monday]

B) Extract Month
1) Input this formula on a separate cell =TEXT(A1,"mmm") [Result; Jan]
2) If you wish to have a full length month, you can use 8 letters instead of 3 like this
=TEXT(A1,"mmmmmmmm") . [Result: January]C) Extract Year
1) Input this formula on a separate cell =TEXT(A1,"yy") [Result; 13]
2) If you wish to have a full length year, you can use 8 letters instead of 3 like this
=TEXT(A1,"yyyy") . [Result: 2013]