Spell a Date out in Words

[Rick Rothstein]

Nice one Rick as usual

Thank you, although I haven't really posted in this forum enough to have an "as usual" yet, have I?

A minor bug, for the period 1/1/1900 - 2/29/1900 shows a day prior.

eg:

1/1/1900 & 2/29/1900, shows "Thirty-first of December, One Thousand Eight Hundred Ninety-Nine" & "Twenty-eighth of February, One Thousand Nine Hundred" respectively.

Actually, there is no bug in my code (if you call the function from within other VB code, you will see it works correctly). There is a bug at work here though, but it is in Excel, not my code. Excel thinks that the year 1900 was a leap year meaning Excel thinks there was a February 29, 1900 when in reality there was no such date, my code (which does know 1900 was not a leap year) ends up reporting a day earlier for January and February 1900. Given this problem exists only for a set of dates that next to nobody will ever seek to display as words, I am not sure it is worth trying to fix... I think about trying to fix it though (not as straightforward a task as it might seem). Anyway, see here for the reason behind this Excel bug...

Excel 2000 incorrectly assumes that the year 1900 is a leap year

Based on your logic, I think we could also use it by native formulas, with help of 2 lookup tables.

That seems to work... nicely done! My preference is still for the UDF... less overhead in Excel "real estate" (meaning it is easily reusable in multiple workbooks) plus it can be called by other VB code where it can cover dates earlier than 1/1/1900 if desired.