Bookmark page
Printable version
Course updatesForum home » Delegate support and help forum » Microsoft VBA Training and help » access vba training - Date formats in Functions
|
|
|
Bookmark this page|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
access vba training - Date formats in Functions
Resolved · Low Priority · Version Standard
Reply
Date formats in Functions
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
For upcoming training course dates see: Pricing & availability
RE: Date formats in Functions
Hi JamesAll 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
RE: Date formats in Functions
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
RE: Date formats in Functions
JamesYes 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
RE: Date formats in Functions
Thanks Carlos,Trust Microsoft to make things difficult!!!
James
|
 |
|
» Forum post: Slide Master |
How do I add a logo to each page?
