Nested IF Statement

[BradCustom]

Hi Everyone,

I'm working on a formula where I could use some help. I'm using Crystal 2008 with a SQL Database.

I'll try and explain what I'm trying to do with this formula.

I have a table called Attendance which has two fields that I'm working with {Attendance.Login} and {Attendance.Logout}. There are of course many employees and 6 different shifts. The Shifts are "DAY6AM", "DAY6:30AM", "DAY7AM", "DAY8AM", "NIGHT3PM", "NIGHT3:30PM" and "NIGHT4PM"

What I'm trying to do is if an employee’s Login time falls within a time range then report it as the start of Shift but if it is outside that range then report the actual login time. I've come up with a formula that works for one condition; I'm having a problem combining the different conditions into one formula.

Below is a formula for an Employee with a Shift of "DAY7AM" and I'm testing the Login time.

If {Shift.Shift_Name}= 'Day7AM'
then
(
if ({Attendance.Login}>({Attendance.Work_Date}+.27430556) and
{Attendance.Login}<({Attendance.Work_Date}+.296527778))
then
{Attendance.Work_Date}+ .29166667
else
{Attendance.Login}
)

Thanks for your help!

 

[MCuthill]

Hey Brad,

Just an idea which may help.

If {Shift.Shift_Name}= 'Day7AM'
then
(
if ({Attendance.Login}>Time(07,00,00) and
{Attendance.Login}<(Time(EndHour,00,00))
then
Time(07,00,00)
else
{Attendance.Login}
)

"End Hour" is the expected end time of the shift. Your structure looks fine, are you getting an error with your formula?

Hope this helps,

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[BradCustom]

I left one key part out in my previous post. Each Employee typically has two Login and Logout times each day so the ({Attendance.Login}>({Attendance.Work_Date}+.27430556) equates to {Work_Date}6:35AM and {Attendance.Login}<({Attendance.Work_Date}+.296527778) equates to {Work_Date}7:07AM.

So, if an Employee's Login time falls within that range then {Attendance.Work_Date}+ .29166667 would equal {Work_Date}7:00AM. The reason I'm using this approach is because I have a parameter which limits the record selection to a date range. The default for {Attendance.Work_Date} is {Work_Date}12.00AM

I'd also need to test for a Lunch Time Login and Logout which is at 12:00PM to 12:30PM I've created a similar formula to the one in my previous post for each Login and Logout time I'm just having a tough time stringing the different conditions together ie: Shift, Start Login, Lunch Login or Logout; which logout would obviously be a seperate formula.

Thanks for your help!

 

[BradCustom]

Mike,

Thanks for your response; I'm not getting any errors in my formula. It only works for one of a possible 12 conditions. I know I didn't mention it previously but a typical day would be:

Start Login: 7:00AM
Lunch Logout: 12:00PM

Lunch Login: 12:30PM
End Logout: 5:30PM

The example is for an Employee with a "DAY7AM" Shift; obviously different Shifts have different Start, Lunch and End times.

Thanks for your help!

 

[MCuthill]

Brad,

It is a bit out of my scope of Crystal Knowledge... I know if can be done with one rather large IF statement, but I have a feeling that it could be better served by a CASE statement. Unfortunately, I have little to no experience with CASE statements in Crystal.

From a structure standpoint, I think you may wish to have 4 formula fields - one for each logout and login, then have all the shifts nested within each formula.

For Example, {@MorningLogin} would be the above formula - you just need to add the outer if for each possible shift scenario. I would still suggest the "Time()" function as it would be more easily interpretted and modified going forward.

{@MorningLogin}
If {Shift.Shift_Name}= 'Day7AM' then
(
if ({Attendance.Login}>Time(06,35,00) and {Attendance.Login}<(Time(7,07,00)) 
then 
Time(07,00,00)
else 
{Attendance.Login}
)else
If {Shift.Shift_Name}= 'Day8AM' then
(
if ({Attendance.Login}>Time(07,35,00) and {Attendance.Login}<(Time(8,07,00)) 
then 
Time(08,00,00)
else 
{Attendance.Login}
)else
...
...
...

You could then calculate hours worked off of these four formula fields.

Hope this helps.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[fisheromacse]

I am not certain CASE is ideal for this situation either, but here is a brief example of how you could attempt to implement it:

Create a formula:
//{@SHIFTCASES}
SELECT {Shift.Shift_Name}
CASE "Day7AM": if ({Attendance.Login}>({Attendance.Work_Date}+.27430556) and
{Attendance.Login}<({Attendance.Work_Date}+.296527778))
then {Attendance.Work_Date}+ .29166667
else {Attendance.Login})
CASE "Day8AM": etc
etc
etc
etc

 

[BradCustom]

Mike,

I understand your example but you lost me on one part; I'm missing what your suggesting the {@MorningLogin} formula should be.

I started going in a much longer path but it's working. I created 12 formulas (one for each condition) and suppressed the ones that didn't apply to the paticular record.

Thanks for your help!

 

[BradCustom]

Mike,

The formula you suggested doesn't appear to work because the field {Attendance.Login} is looking for a "Date Time"

I tried your formula but kept getting errors so I tried a simple version and I get "A date-time is required here"

Below is the formula and the bold section is what causes the error.

{Attendance.Login}>[b]Time(07,07,00)[/b]

Thanks for your help!

 

[MCuthill]

Brad,

I did some quick testing here, please add the Time() function around the other half of the logical equation.

For example:
Time(Attendance.Login}) > Time(07,07,00)

The purpose of the {@MorningLogin} formula is to simply have all cases for the morning start time covered in one formula. Of would then have subsequent formula fields for {@MorningLogout}, {@AfternoonLogin}, and {@AfternoonLogout} - this simplifies 12 formula fields down into 4.

Hope this helps,

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[fisheromacse]

What is the data type for {Attendance.Login}?
How does the raw data appear?

 

[BradCustom]

Thanks Mike,

I was working on the problem yesterday and below is what I cam up with for one condition. I added a new wrinkle which is if an employee's login time is greater than 7:07AM but less than 7:15AM then 7:15AM. Ideally I'd like to round up logins to the next 1/4 hour and round down logout to the previous 1/4 hour. That's something I'm working on today along with the original post.
Below is the formula discribed above

if (Time({Attendance.Login}))<=Time (07,15,00)=true and
(Time({Attendance.Login}))>Time (07,07,00)=true
then ({Attendance.Work_Date}+.302083336)

Thanks for your help!

 

[BradCustom]

The data for {Attendance.Login} appears in the tables as shown below

Attendance.Login
10/6/2010 07:00:00AM

 

[MCuthill]

Hi Brad,

Just some ideas for working with your new approach, may or may not be applicable. Something like the below should shift your start time.

IF Time({Attendance.Login}) in [Time(07,00,00) to Time(07,07,59)] THEN Time(07,00,00) ELSE
IF Time({Attendance.Login}) in [Time(07,08,00) to Time(07,22,59)] THEN Time(07,15,00) ELSE
IF Time({Attendance.Login}) in [Time(07,23,00) to Time(07,37,59)] THEN Time(07,30,00) ELSE
IF Time({Attendance.Login}) in [Time(07,38,00) to Time(07,52,59)] THEN Time(07,45,00)

It is just my preference, but I think having a "Time" in the returned value is easier to read than the decimal version -- but really makes no difference.

This could likely be generically revamped to work on any hour (instead of just 7am as above) by looking at just the "Minutes" within a Date-Time field (not tested in Crystal yet though).

IF Minutes({Field}) in [0 to 7] THEN 0 ELSE
IF Minutes({Field}) in [8 to 22] THEN 15 ELSE
IF Minutes({Field}) in [23 to 37] THEN 30 ELSE
IF Minutes({Field}) in [38 to 52] THEN 45 ELSE 0

The returned values could also be the full date-time modified then be used. Could perhaps simplify your original formulas as the time would already be shifted to the "reported start time".

IF Minutes({Field}) in [0 to 7] THEN DateTime(Year({Field}),Month({Field}),Day({Field}),Hour({Field}),0,0) ELSE
IF Minutes({Field}) in [8 to 22] THEN DateTime(Year({Field}),Month({Field}),Day({Field}),Hour({Field}),15,0) ELSE
IF Minutes({Field}) in [23 to 37] THEN DateTime(Year({Field}),Month({Field}),Day({Field}),Hour({Field}),30,0) ELSE
IF Minutes({Field}) in [38 to 52] THEN DateTime(Year({Field}),Month({Field}),Day({Field}),Hour({Field}),45,0) ELSE 
DateAdd("h",1,DateTime(Year({Field}),Month({Field}),Day({Field}),Hour({Field}),0,0))

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[BradCustom]

Mike,

That's funny, I've been working on the same exact approach today although mine is a bit different than yours and so far mine doesn't work

Thanks for your help, I'm going to try it now.

 

[BradCustom]

Ok here is the final formula for this issue. It may not be the cleanest formula but it works. I still have to add 3 addtional shifts to the formula but that's just a case of copying what is already in the formula and changing the numbers based on the shift. Instead of my earlier approach of 12 seperate formulas for login and logout I've condenced it to two formulas; one for login and one for logout. Below is the login formula.

If {Shift.Shift_Name}='DAY6AM' and ({Attendance.Login}>({Attendance.Work_Date}+.232638889) and
{Attendance.Login}<({Attendance.Work_Date}+.254861112))
then
{Attendance.Work_Date}+ .25
else
If {Shift.Shift_Name}='DAY6:30AM' and ({Attendance.Login}>({Attendance.Work_Date}+.258680556) and
{Attendance.Login}<({Attendance.Work_Date}+.275694445))
then
{Attendance.Work_Date}+ .27083334
else
If {Shift.Shift_Name}='DAY7AM' and ({Attendance.Login}>({Attendance.Work_Date}+.27430556) and
{Attendance.Login}<({Attendance.Work_Date}+.296527778))
then
{Attendance.Work_Date}+ .29166667
else
If ({Attendance.Login}>({Attendance.Work_Date}+.5069444445) and
{Attendance.Login}<({Attendance.Work_Date}+.525694445))
then
{Attendance.Work_Date}+ .520833334
else
IF Minute({Attendance.Login}) in [0 to 7] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),0,0) ELSE
IF Minute({Attendance.Login}) in [8 to 15] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),15,0) ELSE
IF Minute({Attendance.Login}) in [16 to 30] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),30,0) ELSE
IF Minute({Attendance.Login}) in [31 to 45] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),45,0) ELSE
IF Minute({Attendance.Login}) in [46 to 59] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),0,0) ELSE 
DateAdd("h",1,DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),0,0))

Thanks for everyones help!

 

[BradCustom]

There is an error at the end of the formula; remove the section below and it will work fine.

IF Minute({Attendance.Login}) in [46 to 59] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),0,0) ELSE

 

[BradCustom]

Below is the complete and correct formula less the DAY8AM shift. I left that shift off because it's comprised of salary employees.

If {Shift.Shift_Name}='DAY6AM' and ({Attendance.Login}>({Attendance.Work_Date}+.232638889) and
{Attendance.Login}<({Attendance.Work_Date}+.254861112))
then
{Attendance.Work_Date}+ .25
else
If {Shift.Shift_Name}='DAY6:30AM' and ({Attendance.Login}>({Attendance.Work_Date}+.258680556) and
{Attendance.Login}<({Attendance.Work_Date}+.275694445))
then
{Attendance.Work_Date}+ .27083334
else
If {Shift.Shift_Name}='DAY7AM' and ({Attendance.Login}>({Attendance.Work_Date}+.27430556) and
{Attendance.Login}<({Attendance.Work_Date}+.296527778))
then
{Attendance.Work_Date}+ .29166667
else
If {Shift.Shift_Name}='NIGHT3PM' and ({Attendance.Login}>({Attendance.Work_Date}+.607638889) and
{Attendance.Login}<({Attendance.Work_Date}+.588194445))
then
{Attendance.Work_Date}+ .5833334
else
If {Shift.Shift_Name}='NIGHT3:30PM' and ({Attendance.Login}>({Attendance.Work_Date}+.62847223) and
{Attendance.Login}<({Attendance.Work_Date}+.650694445))
then
{Attendance.Work_Date}+ .64583334
else
If {Shift.Shift_Name}='NIGHT4PM' and ({Attendance.Login}>({Attendance.Work_Date}+.649305556) and
{Attendance.Login}<({Attendance.Work_Date}+.67152778))
then
{Attendance.Work_Date}+ .6666667
else
If ({Attendance.Login}>({Attendance.Work_Date}+.5069444445) and
{Attendance.Login}<({Attendance.Work_Date}+.525694445))
then
{Attendance.Work_Date}+ .520833334
else
IF Minute({Attendance.Login}) in [0 to 7] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),0,0) ELSE
IF Minute({Attendance.Login}) in [8 to 15] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),15,0) ELSE
IF Minute({Attendance.Login}) in [16 to 30] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),30,0) ELSE
IF Minute({Attendance.Login}) in [31 to 45] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),45,0) ELSE
DateAdd("h",1,DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),0,0))

 

[BradCustom]

I found one omission which has bee add to the formula below. I left out the Night shift login after lunch grace period. The section added is in bold and italic.

If {Shift.Shift_Name}='DAY6AM' and ({Attendance.Login}>({Attendance.Work_Date}+.232638889) and
{Attendance.Login}<({Attendance.Work_Date}+.254861112))
then
{Attendance.Work_Date}+ .25
else
If {Shift.Shift_Name}='DAY6:30AM' and ({Attendance.Login}>({Attendance.Work_Date}+.258680556) and
{Attendance.Login}<({Attendance.Work_Date}+.275694445))
then
{Attendance.Work_Date}+ .27083334
else
If {Shift.Shift_Name}='DAY7AM' and ({Attendance.Login}>({Attendance.Work_Date}+.27430556) and
{Attendance.Login}<({Attendance.Work_Date}+.296527778))
then
{Attendance.Work_Date}+ .29166667
else
If {Shift.Shift_Name}='NIGHT3PM' and ({Attendance.Login}>({Attendance.Work_Date}+.607638889) and
{Attendance.Login}<({Attendance.Work_Date}+.588194445))
then
{Attendance.Work_Date}+ .5833334
else
If {Shift.Shift_Name}='NIGHT3:30PM' and ({Attendance.Login}>({Attendance.Work_Date}+.62847223) and
{Attendance.Login}<({Attendance.Work_Date}+.650694445))
then
{Attendance.Work_Date}+ .64583334
else
If {Shift.Shift_Name}='NIGHT4PM' and ({Attendance.Login}>({Attendance.Work_Date}+.649305556) and
{Attendance.Login}<({Attendance.Work_Date}+.67152778))
then
{Attendance.Work_Date}+ .6666667
else
[b][i]If ({Attendance.Login}>({Attendance.Work_Date}+.88194445) and
{Attendance.Login}<({Attendance.Work_Date}+.900694445))
then
{Attendance.Work_Date}+ .89583334
else[/i][/b]
If ({Attendance.Login}>({Attendance.Work_Date}+.5069444445) and
{Attendance.Login}<({Attendance.Work_Date}+.525694445))
then
{Attendance.Work_Date}+ .520833334
else
IF Minute({Attendance.Login}) in [0 to 7] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),0,0) ELSE
IF Minute({Attendance.Login}) in [8 to 15] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),15,0) ELSE
IF Minute({Attendance.Login}) in [16 to 30] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),30,0) ELSE
IF Minute({Attendance.Login}) in [31 to 45] THEN DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),45,0) ELSE
DateAdd("h",1,DateTime(Year({Attendance.Login}),Month({Attendance.Login}),Day({Attendance.Login}),Hour({Attendance.Login}),0,0))