Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
The "Nineteen Ninety" part is highly non-standard, so most likely you're writing a custom macro to achieve this. What have you tried already? Where are you getting stuck? –  Ƭᴇcʜιᴇ007 Nov 4 '12 at 17:28
    
See this Microsoft Support page for a general currency VBA solution to get you started –  chris neilsen Nov 6 '12 at 8:04
add comment

2 Answers 2

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.

=TEXT(A1,"MMMM")

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

=CHOOSE(DAY(A1), "First", "Second", "Third", "Fourth", ...)

(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

=VLOOKUP(DAY(A1), Sheet2!A1:B31)

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.

share|improve this answer
add comment

Largely covered by @ssollinger, but using a single formula and lookup table:

SU499493 example

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.