Forum Discussion

lange1670's avatar
lange1670
Copper Contributor
Sep 17, 2024

Nesting IF formulas in same cell

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

Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Sep 17, 2024
    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's avatar
    m_tarler
    Steel Contributor
    Sep 17, 2024
    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.

Resources