Convert YYYY to text, or YY to text

[Ochimus]

Anyone know a formula that can take a 'mm/yy' or 'mm/yyyy' cell and write the century and year out in longhand?

So yyyy=1900 would be "Nineteen hundred years ", yyyy=1901 would be "Nineteen hundred and one years", "yy" =18 would be "Two thousand and eighteen years", and so one.

Tried creating a formula that "splits" the number into two, with the first step looking at the LH pair (e.g. 19 or 20) if they exist, then convert the RH pair (e.g. 03, 15,29,31 etc)

Look forward to pointers

Ochimus

[fredfarmer]

Maybe try this:

Please Login or Register  to view this content.

[Pete_UK]

When you say a 'mm/yy' cell, or a 'mm/yyyy' cell, does the cell contain a date which is formatted to display either mm/yy or mm/yyyy, or does the cell contain a text value in one of those formats?

If it is a date, then we can use the YEAR function to extract the year, but if it is a text value we would need to use RIGHT in conjunction with FIND("/" …

Pete

[Ochimus]

Sorry for delayed reply, gents, been out of contact for few days!

The cells are dates formatted to either a four digit or two digit year (checked by converting the cell to "comma style" format. Works perfectly).

But YEAR does not deliver what I want. If A2 is 01/01/2019, and B2 "=YEAR(A2)", B2 will contain four numbers (2019).

I want a formula in B2 that will see the year in the date "01/01/2019" in A2 and enter "Two thousand and nineteen years".

Presumably it will have to identify the century first (e.g. 1999 would be "Nineteen hundred"), THEN identify the two final digits (99) and - as they are more than zero - add them as "and ninety nine years".)

Ochimus

[Pete_UK]

Do you want to see "nineteen hundred" if the century year is 19, or would "one thousand nine hundred" be acceptable?

Pete

[Pete_UK]

Here's a formula that can do it, assuming your (proper) date is in A1, this can go in B1:

=CHOOSE(INT(YEAR(A1)/1000),"One","Two")&" thousand "&IF(MID(YEAR(A1),2,1)="0","",CHOOSE(MID(YEAR(A1),2,1)*1,"one","two","three","four","five","six","seven","eight","nine")&" hundred")&" and "&IF(AND(MOD(YEAR(A1),100)>9,MOD(YEAR(A1),100)<20),CHOOSE(MOD(YEAR(A1),100)-9,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),IF(MID(YEAR(A1),3,1)="0",CHOOSE(MOD(YEAR(A1),100),"one","two","three","four","five","six","seven","eight","nine"),CHOOSE(MID(YEAR(A1),3,1)*1-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&IF(MID(YEAR(A1),4,1)="0",""," "&CHOOSE(MID(YEAR(A1),4,1)*1,"one","two","three","four","five","six","seven","eight","nine"))))

Copy it down if needed.

Hope this helps.

Pete

[Ochimus]

Apologies to everyone for delay in acknowledging the responses, but been guest of the NHS for last few weeks!

Now back, and using Peter's approach can mark this as "Solved"

Ochimus

[Pete_UK]

Thanks for the rep - I hope you are feeling much better now.

Pete