Formula for Specific Parameter Date/Time Range

[kowalbp]

All,

First let me say I'm not a used to working with Crystal Reports. I have so far been able to create basic formulas getting the results I need. But for this issue I need to consult you expertise.

I need some quidance how to create a formula for a specific date range within specific time windows. I'm not sure whether I need 2 (just dates) or 4 (dates and times) parameters. But the concept it is. A parameter field is set asking for a date range.

I only need results for data between the dates entered, between the hours of 7pm to 6:30am (Monday to Friday) or possibly using time parameter fields, and 24 hours a day on Saturday's and Sunday's. I'm assuming that due to split days the time will have to be a military range from 19:00 to 24:00, and 00:00 to 06:30 ???

Here's the basic crystal formula I've been tinkering with (not even tackling the weekend 24 hour issue yet..but can't figure out how to create correctly.

{PWDCounter.ResetDate} in
datetime({?Start Date}, time(19,0,0)) to
datetime({?End Date}, time(23,59,59)) and
{PWDCounter.ResetDate} in
datetime({?Start Date}, time(0,0,0)) to
datetime({?End Date}, time(6,30,00))

But I still keep getting 3pm dates.

Any help or guidance would be great !

Thanks in advance,
Brad

 

[lbass]

For a 7pm to 6:30am shift which starts on a Sunday night, does this count as Monday or as Sunday? In other words, is your weekend 7:00 pm on Friday to 6:59pm on Sunday? Or is it 6:31am on Saturday to 6:31am on Monday? Or?

-LB

 

[kowalbp]

For a 7pm to 6:30am shift which starts on a Sunday night, does this count as Monday or as Sunday? My thoughts are this would be Sunday up until midnight, and Monday midnight onward. If we need to we can consider this to just be a Sunday instance if easier.

In other words, is your weekend 7:00 pm on Friday to 6:59pm on Sunday? Or is it 6:31am on Saturday to 6:31am on Monday? Or?

Preferrably, Weekdays would be defined from 7pm Friday night to 6:30 am Monday morning.

Weekends are the entire 24 hours of the day.

or Monday to Friday 19:00 to 06:30 weekday internal
and 24 hours Saturday 00:00 to Sunday 24:00

Brad

 

[lbass]

You really have only 4 weekday shifts then. So try a formula like this. First make your parameter a date parameter, not datetime.

date({PWDCounter.ResetDate}) in {?StartDate} to {?EndDate} and
if dayofweek({PWDCounter.ResetDate}) in 2 to 5 then
(
time({PWDCounter.ResetDate}) in time(19,0,0) to time(23,59, 59) or
time({PWDCounter.ResetDate}) in time(0,0,0) to time(6,30,0)
) else
if dayofweek({PWDCounter.ResetDate}) = 6 then
time({PWDCounter.ResetDate}) in time(0,0,0) to time(6,30,0) else
true

This would give you data from 7pm to 6:30 am starting Monday at 7pm and ending at 6:30am on Friday, and at any time on Saturday and Sunday.

-LB

 

[kowalbp]

-LB

Thank you very very much. Your code worked perfect.

I did add a line

and
if dayofweek({PWDCounter.ResetDate}) = 1 then
time({PWDCounter.ResetDate}) in time(0,0,0) to time(23,59,59) else
true

In order to get the entire other weekend days data to be included.

Thank you very much !

-BK

 

[lbass]

You shouldn't have needed that extra line, since dayofweek = 1 would result in true for the formula and therefore would return all times on that day, but anyway, I'm glad you got it working.

-LB