Convert Date into words in Excel

Spread the love

Convert date into words in Excel – Microsoft Excel is a powerful tool for numbers to text, numbers to words, get a date of birth, convert date to string, convert date of birth in words, convert date to text month, convert date to words, get age in word, and convert date to text.

How to Convert Date into words in Excel

In order to convert a date into words in Excel, the easy way to understand the working of MS Excel formula just separate years, months, and days from the date. While converting the data into words in Excel, will help you to understand the logic.

The below figure of convert a date into words in Excel is showing that A1 cell text is Date of Birth, B1 cell text is Year, C1 Months, D1 Days, and E1 cell has text Age in Words.

convert a date into words in Excel
convert a date into words in Excel

convert a date into words in Excel – Now we will type any date of birth to convert into words in cell A2. I am using the format of date is as “DD-MM-YYYY”, so I will enter first DD, MM, and YYYY. and you may be using the date format as “MM-DD-YYYY” and you will enter your date according to your format. The format represents MM = months, DD = days, and YYYY as years.

The DATEDIF formula

The DATEDIF formula is used to extract years, months and days. Microsoft Excel doesn’t provide any preview or help when you start type formula in the cell. So, you should be careful while using the formula. Using the DATEDIF formula you can get years, months, and days between two dates. The DATEDIF formula provides you a simple and easy way to get differences between two dates or get age from a date to date.

Syntax and Arguments of DATEDIF formula

  • =DATEDIF (start-date, end-date, unit)
  • Start-date – The first or start date.
  • End-date – the last or ending or the current date.
  • unit – get years only type “y” or months only “ym” or days on “md”.

Type formula in B2 to extract a number of years from A2. The above discussion shows that the first parameter is the start date, the second is the last or current date, and finally is unit. For the current date, we will use TODAY () formula to get the current time of the system, and in units, we will use “y” for years, “ym” for months, and “md” for days. See the below formulas, which are going to use the Excel sheet.

  • =DATEDIF(A2,TODAY(),”y”)
  • =DATEDIF(A2,TODAY(),”ym”)
  • =DATEDIF(A2,TODAY(),”md”)

Now we got Years, Months, and Days in different cells from a cell A1. When you type a new date, MS Excel will automatically separate years, months, and days in formulated cells. The last step of convert date into words in Excel is to apply to choose formula. Just copy and paste the following formula into E2 cell and hit the enter key.

=CHOOSE(LEFT(TEXT(B2,"000"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--LEFT(TEXT(B2,"000"))=0,,IF(AND(--MID(TEXT(B2,"000"),2,1)=0,--MID(TEXT(B2,"000"),3,1)=0)," Hundred"," Hundred and "))
&CHOOSE(MID(TEXT(B2,"000"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(B2,"000"),2,1)<>1,CHOOSE(MID(TEXT(B2,"000"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(B2,"000"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))

You will see the number of B2 is converted into words. This is the main formula to convert numbers into text for date management. Now append &” Years “at the end of the pasted formula, now you will the text is changed into “Thirty Seven Years”

=CHOOSE(LEFT(TEXT(B2,"000"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--LEFT(TEXT(B2,"000"))=0,,IF(AND(--MID(TEXT(B2,"000"),2,1)=0,--MID(TEXT(B2,"000"),3,1)=0)," Hundred"," Hundred and "))
&CHOOSE(MID(TEXT(B2,"000"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(B2,"000"),2,1)<>1,CHOOSE(MID(TEXT(B2,"000"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(B2,"000"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) & " Years "

The next step is to add months to E2 cell. In the above formula just copy it into Notepad, and replace = sign with & and replace B2 cell name with C2 and “ Years” with “ Months “ &

& CHOOSE(LEFT(TEXT(C2,"000"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--LEFT(TEXT(C2,"000"))=0,,IF(AND(--MID(TEXT(C2,"000"),2,1)=0,--MID(TEXT(C2,"000"),3,1)=0)," Hundred"," Hundred and "))
&CHOOSE(MID(TEXT(C2,"000"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(C2,"000"),2,1)<>1,CHOOSE(MID(TEXT(C2,"000"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(C2,"000"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) & " Months and "

Now append the above formula with already existing formula in E2 cell. When you press Enter key it should show “Thirty-seven Years Eleven Months and ”. The final step of convert date into words in Excel is to add days string into E2. The method is the same as repeated with months, just replace C2 with D2 and “Months and “ with “Days”

=CHOOSE(LEFT(TEXT(B2,"000"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--LEFT(TEXT(B2,"000"))=0,,IF(AND(--MID(TEXT(B2,"000"),2,1)=0,--MID(TEXT(B2,"000"),3,1)=0)," Hundred"," Hundred and "))
&CHOOSE(MID(TEXT(B2,"000"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(B2,"000"),2,1)<>1,CHOOSE(MID(TEXT(B2,"000"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(B2,"000"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) & " Years " 
& CHOOSE(LEFT(TEXT(C2,"000"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--LEFT(TEXT(C2,"000"))=0,,IF(AND(--MID(TEXT(C2,"000"),2,1)=0,--MID(TEXT(C2,"000"),3,1)=0)," Hundred"," Hundred and "))
&CHOOSE(MID(TEXT(C2,"000"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(C2,"000"),2,1)<>1,CHOOSE(MID(TEXT(C2,"000"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(C2,"000"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) & " Months and " 
& CHOOSE(LEFT(TEXT(D2,"000"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--LEFT(TEXT(D2,"000"))=0,,IF(AND(--MID(TEXT(D2,"000"),2,1)=0,--MID(TEXT(D2,"000"),3,1)=0)," Hundred"," Hundred and "))
&CHOOSE(MID(TEXT(D2,"000"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(D2,"000"),2,1)<>1,CHOOSE(MID(TEXT(D2,"000"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(D2,"000"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) & " Days "

Translate Date into other Language – convert date into words in Excel

The translation of words, lines, and paragraphs is a great feature in Microsoft Office. You can use this feature under the “Review” tab in Microsoft Word, Excel, PowerPoint, and other MS Office. But the translation into other language matters, either it is translating with correct words or not.

Microsoft Office can translate text into different languages, but it has some issues. While I was working over date translation into other languages, I found that if the date is March 7, 2020, in English words it should seven march and twenty thousand twenty, and in the Urdu language, it should “سات مارچ بیس سو بیس” But it was showing “2020 سات مارچ”. This was the issue in Microsoft language translation.

Google translator is very good and close to human translation. It is absolutely free and easy to use. Google translator another plus point it is fast than Microsoft translator.

[Download file with Report card]

Topic Name: Convert a date into words in Excel

The theme of Convert date into words in Excel article

Convert a date into words in Excel – So hope you got some useful information about numbers to text in excel, numbers to words in excel, get the date of birth in excel, choose formula in excel, left formula in excel, dob in excel, separate days months years in excel, DATEDIF formula.

You can watch the video to understand how to convert a date into words in Excel, Please like, comment, and share the video. Thanks for watching the video.

Leave a Reply

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


*