Filter by time and dayoftheweek?

[rkckjk]

I'm using the following formula that I was given to an earlier question I had:

if time({table.opendatetime}) in time(0,0,0) to time(7,59,59) then "Third Shift" else
if time({table.opendatetime}) in time(8,0,0) to time(15,59,59) then "First Shift" else
if time({table.opendatetime}) in time(16,0,0) to time(23,59,59) then "Second Shift"

The formula worked fine, but now I need to somehow change the formula to work with the following criteria:

Weekends = Saturday 8:00am - Monday 7:00am
FirstShift = Mondays 7:00am - 16:00, Tuesday to Friday 8:00am - 16:00
SecondShift = Monday to Friday 18:00 - 24:00
ThirdShift = Tuesday to Saturday 00:00 - 7:00am

So, now I need to change the formula on four groups. I guess I need to use the ayOfTheWeek function somehow.
Here is some sample data:
3/1/2005 6:47
3/1/2005 8:22
3/1/2005 13:31
3/1/2005 13:37
3/1/2005 14:46
3/1/2005 21:50
3/1/2005 22:30
3/2/2005 4:15
3/2/2005 4:20
3/2/2005 5:54
3/2/2005 8:57
3/2/2005 11:51
3/2/2005 14:37
3/2/2005 20:06
3/2/2005 22:54
3/2/2005 22:54
3/2/2005 23:02
3/3/2005 5:06
3/3/2005 10:17
3/3/2005 12:30
3/3/2005 21:43

 

[lbass]

Try the following:

if (dayofweek({table.datetime}) = 7 and
time({table.datetime}) >= time(8,0,0)) or
dayofweek({table.datetime}) = 1 or
(dayofweek({table.datetime}) = 2 and
time({table.datetime}) < time(7,0,0)) then "Weekend" else

if dayofweek({table.datetime}) in 3 to 7 then
(if time({table.datetime}) in time(0,0,0) to time(7,59,59) then "Third Shift" else

if time({table.datetime}) in time(8,0,0) to time(15,59,59) then
"First Shift" else

if time({table.datetime}) in time(16,0,0) to time(23,59,59) then "Second Shift") else

if dayofweek({table.datetime}) = 2 and time({table.datetime}) in time(7,0,0) to time(15,59,59) then
"First Shift" else
if time({table.datetime}) in time(16,0,0) to time(23,59,59) then "Second Shift" else "Other"

-LB

 

[rkckjk]

It's not picking up First Shift for some reason

 

[lbass]

I tested this, and it works here and picks up the first shift. Did you copy and paste the formula and then do a search and replace to add your datetime field? You might have missed a paren somewhere...

-LB

 

[rkckjk]

I had a typo, I 'm getting the count but it's not displaying the First shift records.

 

[lbass]

You didn't change the order of the clauses did you? Maybe you should copy your formula into the post.

-LB

 

[rkckjk]

here's the formula:
if (dayofweek({Sheet1_.Open}) = 7 and
time({Sheet1_.Open}) >= time(8,0,0)) or
dayofweek({Sheet1_.Open}) = 1 or
(dayofweek({Sheet1_.Open}) = 2 and
time({Sheet1_.Open}) < time(7,0,0)) then "Weekend" else

if dayofweek({Sheet1_.Open}) in 3 to 7 then
(if time({Sheet1_.Open}) in time(0,0,0) to time(7,59,59) then "Third Shift" else

if time({Sheet1_.Open}) in time(8,0,0) to time(15,59,59) then
"First Shift" else

if time({Sheet1_.Open}) in time(16,0,0) to time(23,59,59) then "Second Shift") else

if dayofweek({Sheet1_.Open}) = 2 and time({Sheet1_.Open}) in time(7,0,0) to time(15,59,59) then
"First Shift" else
if time({Sheet1_.Open}) in time(16,0,0) to time(23,59,59) then "Second Shift" else "Other"


I'm getting the right count for all the shifts, but it's not displaying the records for FirstShift

 

[lbass]

I tested your formula against my data and it returns the correct results, including first shift results.

I'm not sure what you mean by "it's not displaying the records for FirstShift". Are you saying that the entire detail records for all First Shift records do not appear? If they are contributing to counts, but not appearing on the report, then you should check the section expert to see if you have conditional suppression in place or the group selection formula to see if you have excluded the First Shift records.

Or do you mean that the formula is not returning "First Shift" on the first shift records? Maybe you should provide some sample results. Also, where are you placing this formula? I assumed that it was a detail level formula.

-LB

 

[rkckjk]

Sorry, about all the trouble I had a selection formula in my crystal report to exclude first shift. It works great now. Thanks a lot for all your help.