Date Functions & Formulas

[Former Member]

Post Author: onebite2

CA Forum: Formula

Given date range for instance, 12/1/07 - 12/25/07. I need to pull every Tuesday in above date range.I need to see 4,11,18,25 on crystal report on passing my date range, is that possible if i provide a date range.12/1/07 - 12/25/07This is how i want to show on crystal report.Dec (Month name)4 11 18 25 (all Tuesday's for the above date range)Note: I might have to pull Tuesday's & Thursdays too but just as an example i said Tuesday's.Thanks,Cindy Note: This is my first post so i'm not sure what's need to be in Tags below

[Former Member]

Post Author: bettername

CA Forum: Formula

No database-ness? Wierd, but fair enough!

This formula will display all the dates that are Tuesdays on seperate lines - make sure you have enabled 'Can Grow' on the properties of the formula once you have placed it on the report. It assumes your date range parameter has the orginal name of {?date range}. Change it to whatever you're using... the last "if...else..." statement is there just in case you want to remove the "TUESDAY BETWEEN..." line, and whatever daterange you give it produces no tuesdays...

[EDIT - removed a formula I just posted. I re-read your first post, this is much better!]

datevar start := minimum({?date range});datevar end := maximum({?date range});

stringvar tuesdays:="TUESDAYS BETWEEN "totext(start)" and "totext(end)chr(10);datevar thisdate;numbervar i;

tuesdays:=tuesdaysmonthname(month(start))chr(10);

for i:=1 to datediff("d",start,end) do(thisdate := date(dateadd("d",i,start));

if month(thisdate) <> month(dateadd("d",-1,thisdate))then (if month(thisdate) = 1 then tuesdays := tuesdays + chr(10)chr(10)monthname(month(thisdate))" "totext(year(thisdate),0,"")+chr(10)elsetuesdays := tuesdays + chr(10)chr(10)monthname(month(thisdate))+chr(10))

else

if month(thisdate) = month(dateadd("d",-1,thisdate))and dayofweek(thisdate) = 3 //change the value here to whatever day you need, or use "in &#91;1,3&#93;" instead of "=3" to pick sundays and tuesdaysthen tuesdays := tuesdays + totext(day(thisdate),0,"") + chr(9); );

if len(tuesdays)>0 then if right(tuesdays,1) = chr(9) then left(tuesdays, len(tuesdays)-1)elsetuesdays;

[Former Member]

Post Author: onebite2

CA Forum: Formula

Thanks for your quick response. Nope i'm not comparing with database fields. Infact i don't have to do anything with the database here.I'll create date range parameter and pass date range something like (12/1/2007-12/29/2007)Crystal report need to display all tuesday's for the above date range.Something like thisDec4 11 18 25That's it. There is no database interaction here. Please send the instruction without using any database parameters.Thanks, Cindy

[Former Member]

Post Author: SKodidine

CA Forum: Formula

There may be a few ways of accomplishing this but here is one:

1. I assume you will be comparing the date range parameter to a date field in a table in the database.

2. In your record selection formula insert the following line:

{table.date} in {?Date Range Parameter} and dayofweek({table.date}) = 3

3. Group by {table.date} and under options choose u2018for each monthu2019.

4. Insert {table.date} in the details section, then right click on it and then do the next three steps:

5. click on X-2 button across from u2018display stringu2019 under the u2018commonu2019 tab.

6. In the formula workshop window type totext({jurorpmt.payment_date},"d") then click on u2018save and closeu2019.

7. In the u2018commonu2019 tab, check the box next to u2018suppress if duplicatedu2019.

8. In design mode, right click on the details section, then on u2018section expertu2019.

9. Check the box next to u2018Format with Multiple Columnsu2019.

10. Once step 6 is done you should see a new tab called u2018Layoutu2019. Click on it and change width to 2.000

11. Click OK.

12. In preview mode, right click on group name and then on the X-2 button across from u2018display stringu2019 under the u2018commonu2019 tab.

13. Type totext({table.date},"MMM") and then click on u2018save and closeu2019.

The report should now look as you requested.