Date Formula Question

[iamkaos]

I'm trying to create a selection date formula for a specific department that operates from 11:00PM to 7:00AM. i currently have this selection formula:

select case {?Relative Date}
case "None"
if DTSToDate ({table.date}) in {?StartDate} to {EndDate} Then
formula = 1
else
formula = 0
end if
case "Yesterday"
if DTSToDate ({table.date}) = CurrentDate - 1 Then
formula = 1
else
formula = 0
end if
case "Week to Date"
if DTSToDate ({table.date}) = minimum (weektodatefromsun) to maximum (WeekToDateFromSun)-1 Then
formula = 1
else
formula = 0
end if
case "Last Week"
if DTSToDate ({table.date}) = minimum (lastfullweek) to maximum (lastfullweek) Then
formula = 1
else
formula = 0
end if
case "Month to Date"
if DTSToDate ({table.date}) = minimum (monthtodate) to maximum (monthtodate)-1 Then
formula = 1
else
formula = 0
end if
case "Last Month"
if DTSToDate ({table.date}) = minimum (lastfullmonth) to maximum (lastfullmonth) Then
formula = 1
else
formula = 0
end if
case "Year to Date"
if DTSToDate ({table.date}) = minimum (yeartodate) to maximum (yeartodate)-1 Then
formula = 1
else
formula = 0
end if
case else
formula = 0
end select

How can I define a day or rewrite this formula so that when I select Yesterday, Last Week, etc crystal knows that a day is equal to 11:00PM to 7:00AM?

 

[lbass]

Do you have a time field to work with? I would use a formula like:

dateadd("h",1,datetime(dtstodate({table.datestring),timevalue({table.timestring)))

...if the 11:00 was considered part of the next day. Or if it is considered part of the previous day, then use:

dateadd("h",-7,datetime(dtstodate({table.datestring}),timevalue({table.timestring)))

Then use the formula instead of:

DTSToDate ({table.date})

in your formula above.

-LB

 

[iamkaos]

It is a date time field. The Department's start time is 11:00PM. So for the instance of "Yesterday", I would use:

Case "Yesterday"
if dateadd("h",-7,datetime(dtstodate({table.datestring}),timevalue({table.timestring))) = Currentdate -1 then
formula = 1
else
formula = 0
??
If so what about for "Month to Date"? Shouldn't I define a day to be 11:00PM last night to 7:00AM this morning?

 

[lbass]

It's your choice whether the shift belongs to the time it ends or the time it begins, but my formulas are forcing the shift to fit into one day or the other, not cross days. Technically, the -7 approach is for 11pm to 6:59:59am. Once you decide which formula to use, you would substitute the same formula everywhere you currently use DTStoDate({table.date}).

-LB

 

[iamkaos]

Ok, I'll try it out tomorrow. Thank you for all of your help.