We help IT Professionals succeed at work.

Remove Weekeends in Date Range

makuletski
makuletski asked
on
I have 2 dates startdate and enddate. I used @Explode to get the datelist in between the start and end dates. My problem is how can I exclude the weekend of that datelist.

Example:

startdate = Jan 4, 2008
enddate = Jan 8 2008
datelist = Jan 4, Jan 5, Jan 6, Jan 7, Jan 8

however Jan 5 and Jan 6 are weekends

how can I remove them from the list (using @Function) so my datelist will only contain Jan 4, Jan 7 and Jan 8?
Comment
Watch Question

Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

Commented:
Okay, suppose this all works in Notes as I think it does, here you go:

list:= @Explode(your_range);
dlist:= @Text(list);
wdlist:= @Transform(dlist; "x";
   @If(@Modulo(@Weekday(@TexttoTime(x)); 7)<=1; x; "")
);
@Trim(wdlist)

But you could also have a look at the @BusinessDays  function...

Author

Commented:
this is the code i used in exploding the dates

@Explode(@TextToTime(@Text(@Date(start_date))+"-"+@Text(@Date(end_date))))

but when i added the code you gave, nothing was displayed

(i use this in a view)

what's wrong with my code?

(start_date and end_date are both time/date values)
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

Commented:
So you have:

list:= @Explode(@TextToTime(@Text(@Date(start_date))+"-"+@Text(@Date(end_date))));
dlist:= @Text(list);
wdlist:= @Transform(dlist; "x";
   @If(@Modulo(@Weekday(@TexttoTime(x)); 7)<=1; x; "")
);
@Trim(wdlist)

The result here is a text list. If you want a date list, the last line in the formula should read
@TextToTime(@Trim(wdlist))

If need be, for debugging, add some @Prompt-statements here and there in the code, like the next line just after setting the variable list:
@Prompt([Ok]; "list"; @Implode(@Text(list); ", "))

Author

Commented:
dlist contains values but if i add the wdlist code...still..nothing is displayed...what could be wrong?
Groupware Consultant
CERTIFIED EXPERT
Commented:
There's one serious error in the formula, but you could have found that while testing in a separate field (as I just did). This one provides the working days correctly:

start_date:= [1-1-2008];
end_date:= [2-2-2008];

list:= @Explode(@TextToTime(@Text(@Date(start_date))+"-"+@Text(@Date(end_date))));
dlist:= @Text(list);
wdlist:= @Transform(dlist; "x";
   @If(@Modulo(@Weekday(@TexttoTime(x)); 7)<=1; ""; x)
);
@Trim(wdlist)

Author

Commented:
did not have access to add field to the form. can only work with the documents in a view, but not on the form. was able to test the latest code you gave. it worked perfectly. also saw the error you mentioned. thanks a lot!
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

Commented:
Thanks for the compliment! I have a junkyard.nsf database where I test everything, there are heaps of old code in it and a lot of forms. Create one, even on Local, it'll help you!!

By the way, did you have a look at @BusinessDays?