Q&A, Dates functions in Excel

Question: I have a date in format: 30/12/2012 is in ‘A1’ cell and I need an output in the form: Today is 30th December 2012. Can you tell me how I can get the output in Excel?

Answer:

First let me give you the exact formula to get the desired output. When you enter the date in ‘A1’ cell, you can copy and paste the following formula in B1:

="Today is "& DAY(A1) & "th " & TEXT(A1,"MMMM")& " " & YEAR(A1)

The output I got is: ‘Today is 30th December 2012’

Now, let me present some of the date formula you might find helpful in manipulating the output in Excel. As you can see in the following table, DAY(), MONTH() and YEAR() do work fine but, TEXT() command has a better control over what you get as output.

Formula Output Remark
= DAY (A1) 30 Day
=TEXT(A1,"D") or "DD" 30 2 digit day value
=TEXT(A1,"DDD") Sun Three letter weekday
=TEXT(A1,"DDDD") Sunday Full weekday name
= MONTH (A1) 12 Month in numeric value
=TEXT(A1,"M") or "MM" 12 Month in numeric value
=TEXT(A1,"MMMMM") D Month name initial
=TEXT(A1,"MMMM") December Full month name
=TEXT(A1,"MMM") Dec Three letters of month
=YEAR(A1) 2012 Year value
=TEXT(A1,"YYYY") 2012 Year value
=TEXT(A1,"Y") or "YY" 12 2 digit year value

date_functions_excel

Advertisement

Leave a Reply

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