I am using the following excel formula in one of my cells:

="USD Balance as at "&DAY(TODAY()) & " " &MONTH(TODAY()) & " " &YEAR(TODAY())

Is it possible to create a function that will fill in the month in text form rather than number?

ie the above creates 5 12 2007 and I want it to show 5 December 2007

Hi James

All you need to do is use the TEXT function on the Month's number value as seen in the reworked formula below. The use of "mmmm" produces the full month while "mmm" only produces the abreviation:

="USD Balance as at "&DAY(TODAY()) & " " & TEXT(MONTH(TODAY()),"mmmm") & " " &YEAR(TODAY())

Hope this helps

Carlos

Hi Carlos,

I tried the above formula but it only seems to return "January" as the month value. This seems to be an excel bug as when I rewrote the formula as:

="USD Balance as at "&DAY(TODAY()) & " " & TEXT(MONTH(A1),"mmmm") & " " &YEAR(TODAY())

I tried several dates in A1 and the formula always returned the value "January"

Any ideas why?

James

James

Yes I have an idea. The TEXT function does not work as I presented it. I didn't notice the problem because I've been messing with my computer and had the date on Jan 2008.

I then checked some books and they all say there is no way of changing the months value to Text.

So I created a Function that takes the date you specify ie Totay() and by checking the Month value retrurns a month's name.

The code is:

Public Function MonthText(MyDate As Date) As String

Dim Monthvalue As Byte

MonthValue = Month(MyDate)

Select Case MonthValue

Case 1
MonthText = "January"

Case 2
MonthText = "February"

Case 3
MonthText = "March"

Case 4
MonthText = "April"

Case 5
MonthText = "May"

Case 6
MonthText = "June"

Case 7
MonthText = "July"

Case 8
MonthText = "August"

Case 9
MonthText = "September"

Case 10
MonthText = "October"

Case 11
MonthText = "November"

Case 12
MonthText = "December"

End Select

End Function

Then your formula would read

="USD Balance as at "&DAY(TODAY()) & " " & MonthText(TODAY()) & " " &YEAR(TODAY())

This should solve your problem

Carlos

Thanks Carlos,

Trust Microsoft to make things difficult!!!

James