Creating Time Brackets

[fionama]

Hi,I'm using Crystal 10, and a SQL server database.
I have a data field (type Varchar) containing a date and time (24H) value (attendance date and time).
I would like to extract that time and create time brackets, i.e.
8am to 2pm,
2pm to 10pm and
10pm to 9am.

I have created a field containing the time using the TimeValue function (i.e. TimeValue {IncidentDetails.AEAttendanceDate}) ).

I would now like to create another formula field containing the time brackets.

Does anyone have any ideas? Many thanks,
Fiona

 

[Madawc]

Try

 if @time >= Time(8.00am)
and @time < Time(2.00pm)
then "Band 1"
else

and so on.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[fionama]

Hi, thanks for replying to this query. The syntax I am using does not recognose the 10pm to 8am time bracket however. All attendances falling between 10pm and 8am are going into the "Other" category. Does anyone have any ideas?

if {@AttendanceTime}>=time("8.00am")
and {@AttendanceTime}<time("2.00pm")
then "8am to 2pm"
Else If {@AttendanceTime}>=time("2.00pm")
and {@AttendanceTime}<time("10.00pm")
then "2pm to 10pm"
else if {@AttendanceTime}>=time("10.00pm")
and {@AttendanceTime}<time("8.00am")
then "10pm to 8am"
Else "Unknown

 

[lbass]

If you have successfully converted your datetime to an actual time datatype, then try the following:

if {@AttendanceTime} >= time(8,0,0)
and {@AttendanceTime}< time(14,0,0)
then "8am to 2pm"
Else If {@AttendanceTime}>= time(14,0,0)
and {@AttendanceTime}<time(22,0,0)
then "2pm to 10pm"
else if {@AttendanceTime}>=time(22,0,0)
and {@AttendanceTime}< time(8,0,0)
then "10pm to 8am"
Else "Unknown"

How well this works might depend on how you are dealing with the date issue, since your last "shift" includes times from two different dates.

-LB

 

[MrBillSC]

I think the above formula for 10:00 pm to 8:00 am should contain and "or" not an "and" statement:

else if {@AttendanceTime}>=time(22,0,0)
or {@AttendanceTime}< time(8,0,0)
then "10pm to 8am"

Mrbill

 

[lbass]

Good catch. I agree.

-LB