subtract 1 day, if monday subtract 3 days from date

[jrhessey]

I'm working on a report and would like to automate the select expert so they don't have to change it everyday. I have figured out how to subtract 1 day from today's date, but if they run the report on a Monday, it won't pull Fridays information. If there is a way also, can we skip holidays? Thanks for any help!

 

[synapsevampire]

Crystal version, the formula currently used are basic things you should include with this post.

Try something like:

if dayofweek(currentdate) = 2 then
currendate-3
else
currentdate-1

-k

 

[jrhessey]

That works for the most part... Here is the formula I have entered right now...

{Table1.Crimper} = {?Machine Select} and
{Table1.Date} = if dayofweek(currentdate) = 2 then
currentdate-3
else
currentdate-1

when I hit the check formula button in the formula editor it says no errors found. I save and close it. I then go back in the select editor, click the date tab and it says pops up an error that says "a boolean is required here." and erases the formula. The version I'm working with is 8.5. Thanks for your help!

 

[scottostanek]

Use this as a formula to find date to compare
@CompareDt:
{Table1.Date} = if dayofweek(currentdate) = 2 then
currentdate-3
else
currentdate-1

Select could be
{Table1.Crimper} = {?Machine Select} and
@CompareDt = <someDatefield>

That is your problem, the second half of the select is returning a date not a true or false like the first half.


Scotto the Unwise

 

[lbass]

Try:

{Table1.Crimper} = {?Machine Select} and
(
if dayofweek(currentdate) = 2 then
{Table1.Date} = currentdate-3
else
{Table1.Date} = currentdate-1
)

-LB

 

[jrhessey]

I used lbass's formula, simply because I understood what it did. Thanks for your help though Scotto. I want to add a list of our holidays and this is what I have so far...

(
if dayofweek(currentdate) = 2 then
{Table1.Date} = currentdate-3
else if
currentdate = Date (2006,01,13)
then
{Table1.Date} = currentdate-4
else
{Table1.Date} = currentdate-1
)

I went to add multiple holidays by adding an

or Date (2006,07,04)

(
if dayofweek(currentdate) = 2 then
{Table1.Date} = currentdate-3
else if
currentdate = Date (2006,01,13) or
Date (2006,07,04)
then
{Table1.Date} = currentdate-4
else
{Table1.Date} = currentdate-1
)

and I get an error "a boolean is required here." The formula works with one date, but not more than one. What did I do wrong?

 

[jrhessey]

Nevermind... I think I got it. If I add [ ] around the dates it works, is that the correct way to do it?

 

[lbass]

But holidays can occur on days other than Mondays. The following will get you close:

datevar array holidays := [date(2006,01,01),date(2006,01,16), date(2006,12,25)]; //add all holidays into array
datevar x;
datevar y;

if dayofweek(currentdate) in 3 to 6 then
x := currentdate -1 else
if dayofweek(currentdate) = 2 then
x := currentdate - 3;

if not(x in holidays) then
y := x else
if x in holidays then
(
if dayofweek(x) = 2 then
y := x - 3 else
y := x - 1
);
{table.date} = y and
{Table1.Crimper} = {?Machine Select}

-LB