SOLVED

Nesting IF formulas in same cell

Copper Contributor

I'm not great at writing formulas and usually find answers on forums like this to help me.  I'm trying to put two formulas in one cell.

 

My issue:  

Goal 1.  I want to only add two cells if they both have values (i have figured out the formula for this: =IF(OR(ISBLANK(K14),ISBLANK(I14)),"",K14-I14 and it works fine.

 

Goal 2.  I want to format the cell to subtract time to get total shift time, even if it's after midnight.  The problem here is, it won't calculate correctly if the time goes after midnight.  Example: shift started at 11 pm, and ended at 1 am... I have to enter a special formula to correctly formula after midnight. I do have a working formula for that: IF(I15>K15,K15+1,K15)-I15

 

NOW.... how do combine both formulas so I get the total if both values are present ANNNND format it correctly if the time goes past midnight.

 

My brain hurts - please help

2 Replies
best response confirmed by mathetes (Silver Contributor)
Solution
so you can simply insert that second formula in where K14 is in the first:
=IF(OR(ISBLANK(K14),ISBLANK(I14)), "", IF(I14>K14,K14+1,K14)-I14)
but here is another alternative:
=IF(OR(ISBLANK(K14),ISBLANK(I14)), "", (I14>K14)+K14-I14)
in this latter I just use the condition (I14>K14) which will convert to 0 (false) or 1 (true) because it is in the equation.

@m_tarler Wow - that worked - you are a genius! Thank you!

1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution
so you can simply insert that second formula in where K14 is in the first:
=IF(OR(ISBLANK(K14),ISBLANK(I14)), "", IF(I14>K14,K14+1,K14)-I14)
but here is another alternative:
=IF(OR(ISBLANK(K14),ISBLANK(I14)), "", (I14>K14)+K14-I14)
in this latter I just use the condition (I14>K14) which will convert to 0 (false) or 1 (true) because it is in the equation.

View solution in original post