If condition needs a modification

[#1]

Hi,

I have a table where start time and end time with date available.

St Dt	st Time	end dt	end time
11/11/11	8:20:12 AM	11/11/11	10:00:20 PM
11/11/11	6:20:12 AM	11/12/11	8:00:20 PM
11/11/11	10:20:12 AM	11/15/11	6:00:20 PM
11/11/11	2:20:12 PM	11/11/11	5:00:20 PM

In the chart,I am trying to do some calculatation on those time range which belong to 7AM to 7PM.Expected result would be

St Dt	St time	end dt	end time	calc
11/11/11	8:20:12 AM	11/11/11	10:00:20 PM	exceed Range 7 AM to 7 PM
11/11/11	6:20:12 AM	11/12/11	8:00:20 PM	exceed Range 7 AM to 7 PM
11/11/11	10:20:12 AM	11/15/11	6:00:20 PM	calculated value
11/11/11	2:20:12 PM	11/11/11	5:00:20 PM	calculated value

I have two variables

Set vStart=maketime(7);

Set vEnd=maketime(19);

For the Calc I am using

if (Start_Time < $(vStart) or End_time >$(vEnd),'exceed Range 7 AM to 7 PM',Time(Tot_Hrs-NonWork_Hrs) ) as calc;

the above if condition is not working.Can someone help on this?

Thanks,Pooja

[brenner_martina · Accepted solution]

Hi, Pooja,

your script must look like this:

let vStart=Num(maketime(7));
let vEnd=Num(maketime(19));

LOAD
Start_Date,
Start_Time,
End_Date,
End_Time,
if (Num(Start_Time) < $(vStart) or Num(End_Time) >$(vEnd),'exceed Range 7 AM to 7 PM',Time(End_Time-Start_Time) ) as calc

INLINE [
    Start_Date, Start_Time, End_Date, End_Time
    11.11.2011,  8:20:12 AM, 11.11.2011, 10:00:20 PM
    11.11.2011,  6:20:12 AM, 11.12.2011,  8:00:20 PM
    11.11.2011, 10:20:12 AM, 15.11.2011,  6:00:20 PM
    11.11.2011,  2:20:12 PM, 11.11.2011,  5:00:20 PM
];

Greetings from Germany

Martina

EVACO GmbH

[#3]

Thanks a lot!