Formula to get expected output

[PNC97]

I'm using a formula to calculate OT by the below logic 

if [OT] > 0 and [duplicat] = "ok" then

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] <= [tNight.Start] then 0 else

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] > [tNight.Start] then

(if [Punch-Out] >=24 then 24 else [Punch-Out]) - [tNight.Start] endif

else

(if [Punch in- OT] >= [tNight.Start] and [Punch in- OT] < 24 and [Punch-Out] >= 24 then 24 - [Punch in- OT] else

(if [Punch in- OT] >= [tNight.Start] and [Punch-Out] <24 then [Punch-Out] - [Punch in- OT] else 0
endif)
endif)
endif)
endif)

else 0 endif

I'm getting the output as 24 but expected output is 2 

I have attached the data of input and expected output.

Thanks in advance 🙂

[T_Willins]

Hi @PNC97,

First, I'm not sure how you were getting results as your formula syntax was incorrect.  I corrected it to below (bold added for emphasis for discussion below):

If [OT]<=0 THEN 0 ELSEIF
[duplicat] != "ok" THEN 0 ELSEIF
[Punch in- OT] <= [tNight.Start] AND [Punch-Out] <= [tNight.Start] THEN 0 ELSEIF
[Punch in- OT] <= [tNight.Start] AND [Punch-Out] > [tNight.Start] AND [Punch-Out1] >=24 THEN 24 ELSEIF
[Punch in- OT] <= [tNight.Start] AND [Punch-Out] > [tNight.Start] AND [Punch-Out1] <24 THEN [Punch-Out1] - [tNight.Start] ELSEIF
[Punch in- OT] >= [tNight.Start] AND [Punch in- OT] < 24 and [Punch-Out1] >= 24 then 24 - [Punch in- OT] ELSEIF
[Punch in- OT] >= [tNight.Start] and [Punch-Out] <24 then [Punch-Out1] - [Punch in- OT] ELSE 0
ENDIF

All the records in the sample provided fall out under the bolded section above, which provides a result of 24.  Hopefully this makes it easier for you to correct the formula, but if not, please provide further clarification on how the formula should be calculating.  IF/THEN/ELSEIF formulas in Alteryx will always follow the format of IF... THEN... ELSEIF... THEN... (Repeat ELSEIF/THEN as needed) ELSE... ENDIF

[Maskell_Rascal]

Hi @PNC97 

To piggyback off of what @T_Willins's response, here is your formula written out in Excel. It is operating exactly as expected with the results coming back as "24". Your operation of logic is off. 

[PNC97]

@T_Willins  

I had used the formula like this 

if [OT] > 0 and [duplicat] = "ok" then

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] <= [tNight.Start] then 0 else

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] > [tNight.Start] then

if [Punch-Out] >=24 then 24 else [Punch-Out] - [tNight.Start] endif

else

(if [Punch in- OT] >= [tNight.Start] and [Punch in- OT] < 24 and [Punch-Out] >= 24 then 24 - [Punch in- OT] else

(if [Punch in- OT] >= [tNight.Start] and [Punch-Out] <24 then [Punch-Out] - [Punch in- OT] else 0
endif)
endif)
endif)
endif)

else 0 endif

but using this formula I'm getting 24 as output but the expected output of PQ is 2 as output.

[PNC97]

I applied the formula in power query 

if [OT] > 0 and [duplicat] = "ok" then

(if [#"Punch in- OT"] <= [tNight.Start] and [#"Punch-Out"] <= [tNight.Start] then 0 else

(if [#"Punch in- OT"] <= [tNight.Start] and [#"Punch-Out"] > [tNight.Start] then (if [#"Punch-Out"] >=24 then 24 else[#"Punch-Out"])-[tNight.Start] else

(if [#"Punch in- OT"] >= [tNight.Start] and [#"Punch in- OT"] < 24 and [#"Punch-Out"] >= 24 then 24-[#"Punch in- OT"] else

(if [#"Punch in- OT"] >= [tNight.Start] and [#"Punch-Out"] <24 then [#"Punch-Out"]-[#"Punch in- OT"] else 0))))

else 0

and the output i got is below 

[Maskell_Rascal]

Hi @PNC97 

If you'd like us to help with the formula, please explain the IF/THEN order of logic we need to follow. It is unclear from the formulas you provided, since they were not written the way a typical Alteryx formula would be structured. 

[PNC97 · Accepted solution]

Got solution with the below formula.

if [OT] > 0 and [duplicat] = "ok" then

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] <= [tNight.Start] then 0 else

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] > [tNight.Start] then

(if [Punch-Out] >=24 then 24 else [Punch-Out] endif) - [tNight.Start]

else

(if [Punch in- OT] >= [tNight.Start] and [Punch in- OT] < 24 and [Punch-Out] >= 24 then 24 - [Punch in- OT] else

(if [Punch in- OT] >= [tNight.Start] and [Punch-Out] <24 then [Punch-Out] - [Punch in- OT] else 0
endif)
endif)
endif)
endif)

else 0 endif

Thanks all 🙂