IF to Exclude a value in a basic subtraction

[pete234]

I'm not in front of the formula but I want to subtract two time values but have a zero result if the difference is greater than 1 hour but < 1:30.

I tried an IF statement earlier but it did not work saying I didn't have enough arguments. Basically IF ([Value1]}-{[Value2])> 50 mins but less than 1.5 hours, result is 0, otherwise the result is the operation.
It sounds a bit nuts I'm sure but it is what I'd like to do. I would really like to avoid creating another column to accomplish this if possible. And you might guess, I'm trying to account for lunch time which isn't a set schedule.

 

[pooja_deshpande]

put this formula in the third column. this will give result in minutes

"=IF(AND(((HOUR(E4)-HOUR(D4))*60+MINUTE(E4)-MINUTE(D4))>50,((HOUR(E4)-HOUR(D4))*60+MINUTE(E4)-MINUTE(D4))<90),0,(HOUR(E4)-HOUR(D4))*60+MINUTE(E4)-MINUTE(D4))"

 

[Laurent C]

"=IF(AND(((HOUR(E4)-HOUR(D4))*60+MINUTE(E4)-MINUTE(D4))>50,((HOUR(E4)-HOUR(D4))*60+MINUTE(E4)-MINUTE(D4))<90),0,(HOUR(E4)-HOUR(D4))*60+MINUTE(E4)-MINUTE(D4))"

Datetime values in PowerPivot and Excel are stored as "number of days" since Day 0. So, you do not need to use HOUR and MINUTE functions for this sort of comparison. It is enough to remember that 1 day = 24 hours and 1 hours = 60 minutes.

In PowerPivot, assuming you have a row context:
IF ([End Time] - [Start Time] <= 1.5/24 && [End Time] - [Start Time] > 50 / 24/ 60
,0
, [End Time] - [Start Time]
)

 

[pete234]

Laurent this is working thank you, but I realized I didn't need the limit so anything over 50 minutes is zero.
But I would like to include [Next Start] that are Empty. I did this in another formula like this =IF([Next Start]>0,[Next Start]-[End Time],0)

So I need to combine that to my modified version of yours =IF ([Next Start] - [End Time] >= 50/24/60
,0
, [Next Start] - [End Time])

Any ideas?

 

[Laurent C]

You can nest IF functions if you want, or you can use
SWITCH(TRUE(),
expression1, value1,
expression2, value2,
...
)

 

[pete234]

I am not very good at nesting IF.
I tried this but it doesn't work.
I also tried to use ISBLANK but not sure.
Each is syntax error with no hints.

=IF (([Next Start]) - [End Time] >= 50/24/60,IF([Next Start]>0,[Next Start]-[End Time]
,0
, [Next Start] - [End Time])

I tried it without the repeating subtraction and it didn't change.

=IF ([Next Start]) - [End Time] >= 50/24/60, IF ([Next Start]>0,[Next Start]-[End Time]
,0)

 

[Laurent C]

In that case, a nested formula is not required:
=IF ([Next Start] - [End Time] >= 50/24/60 || ISBLANK([Next Start])
, 0
, [Next Start] - [End Time]
)

should be ok.

|| stand for OR
&& for AND.

 

[pete234]

I meant to say this worked perfectly and I like the syntax of AND OR.
Still learning bit by bit.
Of course, business requirements changed so I had to go back to my old formula and create more columns to get what was needed.
Thanks though.