I want to convert date "dd/mm/yyyy" in to word form like 01/12/1990 to First December Nineteen ninety". I have to convert large data of my students for issuing T C. Can anybody help me?
There is no built-in Excel function to convert dates into text in the way you need it.
There are a number of possibilities how to do this. The easiest way to do it is to use a VB macro, as techie007 suggested.
If you want to do it without macros, the following may help.
month: That's easy. You can display the month as text by using custom formatting (enter "MMMM" as custom formatting). As part of a formula you can do this with =TEXT(...), e.g.
will convert the date in A1 into a month name.
There is no formatting to present the day (except weekday, but that's not what you asked) or year as text. I can think of two functions you could use to look up the text:
You can use =CHOOSE(...) or =VLOOKUP(...). Again, let's assume the date is in A1, you could get the day as a word by using
(add the remaining words instead of "...")
You can do something similar for the year, obviously limiting to the years that may occur.
For VLOOKUP you would make a list somewhere (e.g. on another sheet), first column contains the numbers (e.g. 1, 2, 3, ... for day), second columns the words (First, Second, Third, ...). Assume this list is on Sheet2 in cells A1:B31, the formula to lookup the day would be
Work out day, month and year as explained above and concat them using & symbol.
I would go with the suggestion of techie007 though and use a VB macro to do this.
Largely covered by @ssollinger, but using a single formula and lookup table: