Help

How to modify only one specific part of the formula IF - example below

Topic Labels: Formulas
Solved
Jump to Solution
329 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Jay108
4 - Data Explorer
4 - Data Explorer

Ok so here's the main formula I'm trying to use for my "Monday Pay Hours" field

IF({Monday Start} = BLANK(), 0,DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes'))/60-0.5
This gives me the exact hours worked, minus a half hour lunch break
 
It's almost perfect, but what I need is to tell it that - IF the formula all the way up to here:
 IF({Monday Start} = BLANK(), 0,DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes'))/60
equals less than 5, then don't add the last step of subtracting the 0.5. 
 
Is this possible?
1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

Oh, I still hadn't answered your previous question.

How about a formula like this?

IF(
  AND({Monday Start},{Monday End}),
  IF(
    DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes')/60 >= 5,
    DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes')/60 - 0.5,
    DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes')/60
  ),
  0
)

 

See Solution in Thread

2 Replies 2
Sho
11 - Venus
11 - Venus

Oh, I still hadn't answered your previous question.

How about a formula like this?

IF(
  AND({Monday Start},{Monday End}),
  IF(
    DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes')/60 >= 5,
    DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes')/60 - 0.5,
    DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes')/60
  ),
  0
)

 

@Sho Thank you SO much!!! This worked like a charm! I don't entirely understand it, but it works!

I've been tearing my hair out trying to solve this one for days. Nailed it!