Convert Calendar Date

[JonathanNYC]

Any suggestions for converting a calendar date to the week/day of the month that it falls in a given month.

Example:

January 8, 2007 to be represented as "2nd Monday in January"
January 18, 2007 to be represented as "3rd Thursday in January"
etc.

I know how to format January 8, 2007 so that it appears as Monday, Janauary 8, 2007, but I need week in a given month as well.

I am using CR10.

 

[BobSuruncle]

There may be an easier way, but try this. . .

Local DateVar dt:= Date({Orders.Order Date}) ;
Local NumberVar d ;
Local StringVar name ;
Local StringVar dy ;
Local StringVar suffix ;

d:=DateDiff ("ww",DateSerial(Year(dt),Month(dt),1-1) ,dt, DayOfWeek (dt) ) ;
dy := ToText(d,0,'') ;
name := WeekdayName (DayOfWeek (dt)) ;
suffix := (
select d
case 1 : "st"
case 2 : "nd"
case 3 : "rd"
case 4 to 7 : "th"
);
dy & suffix & " "& name & " in " & MonthName(Month(dt));

Replace {Orders.Order Date} with your DateTime field.

Bob Suruncle

 

[JonathanNYC]

Thanks Bob, looks very promising, I am getting an error message "the ) is missing". I can't yet determine where to place that ) that is missing. Also, do I need to create a formula for weekday and day of the week - I know how to do that, if necessary. Much thanks for this formula.

 

[BobSuruncle]

With version 10, everything should just work as is. (he said hopefully)
WeekdayName and DayofWeek are built-in functions. No extra formula is required.

Where is the cursor flashing when the error message is displayed?

Please paste in your formula.

Thnaks.

Bob Suruncle

 

[JonathanNYC]

Bob, thanks for the perfect formula. Works perfectly.
I tried to use the formula in CR8.5 at home last night, but used your formula in CR10 this morning and it works like a gem. Much appreciated and thanks again for the elegant formula. Jonathan