how can i format a date that uses "1st" or "2nd" or "3rd" or 4th".

how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"...  i am 
working on a program that generates certificates.  i want to use dates such 
as "3rd of April 2010". when i run my program the words that appears onthe 
screen was 3-Apr-10.  can you help me solve this problem?
0
Utf
4/6/2010 3:47:01 AM
access.reports 4434 articles. 0 followers. Follow

1 Replies
762 Views

Similar Articles

[PageSpeed] 16

On Mon, 5 Apr 2010 20:47:01 -0700, cristin wrote:

> how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"...  i am 
> working on a program that generates certificates.  i want to use dates such 
> as "3rd of April 2010". when i run my program the words that appears onthe 
> screen was 3-Apr-10.  can you help me solve this problem?

You can call this function. 
Copy and paste it into a Module. Watch out for word wrap on the longer
lines. 
I've set it to return the date in the form of
5th of April, 2010
for today's date.
You can also display the date in several other formats. Simply comment
out the current line of code and un-comment one of the other lines, as
wanted.

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. Novermber 13th, 2000
' MoIn determines Month Format, i.e. "mmm" for "Feb" or "mmmm" for
"February"

If IsNull(DateIn) Then
    DateOrdinalEnding = ""
    Exit Function
End If
Dim dteX As String

dteX = DatePart("d", DateIn)

dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")

 '  November 13th, 2000
' DateOrdinalEnding = Format(DateIn, MoIn) & " " & dteX & ", " &
Format(DateIn, "yyyy")
' **********************
' Tuesday, November 25th, 2008
' DateOrdinalEnding = Format(DateIn, "dddd, " & MoIn) & " " & dteX &
", " & Format(DateIn, "yyyy")
' **********************
' 17th day of September 2003'
'DateOrdinalEnding = dteX & " day of " & Format(DateIn, " " & MoIn & "
yyyy")
'***********************
 ' Friday 4th of  July, 2008
' DateOrdinalEnding = Format(DateIn, "dddd") & " " & dteX & " of " &
Format(DateIn, " " & MoIn & ", yyyy")
'***********************
  ' Friday 4th July, 2008
' DateOrdinalEnding = Format(DateIn, "dddd") & " " & dteX & " " &
Format(DateIn, " " & MoIn & ", yyyy")
' **********************
' 4th of  July, 2008
 DateOrdinalEnding = dteX & " of " & Format(DateIn, " " & MoIn & ",
yyyy")

End Function

Call it from a query
Exp: DateOrdinalEnding([DateField],"mmmm")
or directly in the control source of an unbound control, on a form or
in a report:
= DateOrdinalEnding([DateField],"mmmm")

-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
4/6/2010 4:35:14 AM
Reply:

Similar Artilces:

how can i format a date that uses "1st" or "2nd" or "3rd" or 4th".
how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am working on a program that generates certificates. i want to use dates such as "3rd of April 2010". when i run my program the words that appears onthe screen was 3-Apr-10. can you help me solve this problem? On Mon, 5 Apr 2010 20:47:01 -0700, cristin wrote: > how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am > working on a program that generates certificates. i want to use dates such > as &q...

showing dates, e.g. 1st, 2nd, 3rd with st, nd, rd in superscript
I've recently started using Word 2007. I prefer to write dates in the form 1st January, rather than January 1. My previous edition of word always automatically gave st, nd and rd as smaller font superscripts or suffixes. I can't find a way of getting Word 7 to do that as a default. See the Date Fields with Ordinals section of the following page of fellow MVP Graham Mayor's website: http://www.gmayor.com/formatting_word_fields.htm -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid p...

Format numbers (ie 1st, 2nd, 3rd, 4th, 22nd, etc)
I have a column of numbers which are the result of determining the number of years between two dates. I need to display a formatted results (ie: 1st, 2nd, 3rd, 4th, 22nd, etc.). Is there a way to do this? Any suggestions would be appreciated. -- Thanks, Graham S Hi Graham, See http://www.cpearson.com/excel/ordinal.htm -- Kind regards, Niek Otten "Graham S" <GrahamS@discussions.microsoft.com> wrote in message news:F8A35936-5C72-4493-9F6D-464A4DE278B8@microsoft.com... >I have a column of numbers which are the result of determining the number >of > years b...

Formula to get 1st,2nd,3rd,4th,5th Sundays from month end date.
Need cell formula or UDF that will return the 1st, 2nd, 3rd, 4th, or 5th Sunday's dates in cells when the month end date is given in a cell. For example: In cell P1, a date of 10/31/05 is entered. I need the following dates: In cell F3 10/02/05 (the 1st Sunday in October) In cell H3 10/09/05 (the 2nd Sunday in October) In cell J3 10/16/05 (the 3rd Sunday in October) In cell L3 10/23/05 (the 4th Sunday in October) In cell N3 10/30/05 (the 5th Sunday in October) Any formula suggestions would be greatly appreciated. Mikeburg -- mikeburg --------------------------------------------...

qry for dates in 1st 2nd 3rd and forth quarters of current year!
Hello i am looking to write a query which returns values for the current year only! DatePart("q",[Start Date],Year([start date])=Year(Date())) is what i have written but this includes enteries for next year which i dont need! any ideas please!!! thanks in advance hi, smason wrote: > DatePart("q",[Start Date],Year([start date])=Year(Date())) > is what i have written but this includes enteries for next year which i > dont need! any ideas please!!! You need to filter for the current year, e.g. SELECT DatePart("q",[Start Date]) FROM [yourT...

How to Determine 1st, 2nd & 3rd for a PWD
I have a spreadsheet that has some times for a Pinewood Derby (PWD). I would like to determine from a range who came in 1st(figured out) 2nd, and 3rd. I figured out 1 by using =MIN(B8:B19) command, but I don't know how to get 2nd and 3rd from the same range. Pete Answered elsewhere...check out the RANK function (No need to multi-post) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Pete n PWD Land" <PetenPWDLand@discussions.microsoft.com> wrote in message news:25E23709-2871-4D0A-9809-49796221CD04@microsoft.com...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...