Adapt formula to use different cells if original source is blank

Options

Hi guys,


Once again I am hoping for some help. I have a formula that is working for me, however I have now added another couple of columns and want to adapt it slightly.

The original formula is


=((VALUE(LEFT([Task End Time]@row, FIND(":", [Task End Time]@row) - 1)) + VALUE(RIGHT([Task End Time]@row, 2)) / 60) + ([Task End Date]@row - [Downtime Start Date]@row) * 24) - (VALUE(LEFT([Downtime Start Time]@row, FIND(":", [Downtime Start Time]@row) - 1)) + VALUE(RIGHT([Downtime Start Time]@row, 2)) / 60)


I would like to change it so it ONLY uses

Downtime Start Date

Downtime Start Time


IF they have values


However, IF either of these cells is blank then use


Downtime Start Date from New Job

Downtime Start Time from New Job


Tried messing with IF(ISBLANK) but getting lost on my syntax and even if this is possible.


As ever any assistance is very much appreciated

Best Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    I included and extra IF. Try:

    =IF(AND(ISDATE([downtime start date]@row), NOT(ISBLANK([downtime start time]@row))), ((VALUE(LEFT([Task End Time]@row, FIND(":", [Task End Time]@row) - 1)) + VALUE(RIGHT([Task End Time]@row, 2)) / 60) + ([Task End Date]@row - [Downtime Start Date]@row) * 24) - (VALUE(LEFT([Downtime Start Time]@row, FIND(":", [Downtime Start Time]@row) - 1)) + VALUE(RIGHT([Downtime Start Time]@row, 2)) / 60),((VALUE(LEFT([Task End Time]@row, FIND(":", [Task End Time]@row) - 1)) + VALUE(RIGHT([Task End Time]@row, 2)) / 60) + ([Task End Date]@row - [Downtime Start Date from New Job]@row) * 24) - (VALUE(LEFT([Downtime Start Time from New Job]@row, FIND(":", [Downtime Start Time from New Job]@row) - 1)) + VALUE(RIGHT([Downtime Start Time from New Job]@row, 2)) / 60)

    Work now?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Glad I could help Dan. Thank you for contributing to the Community. Please accept my answer to close the discussion. (I work for Vote Up and Insightful clicks.)

    Be well,

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi,

    I can get you started but I'm not sure what you mean by instead use New Job.


    You'll start your equation with:

    =IF(AND(ISDATE([downtime start date]@row), NOT(ISBLANK([downtime start time]@row))), IF((VALUE(LEFT([Task End Time]@row, FIND(":", [Task End Time]@row) - 1)) + VALUE(RIGHT([Task End Time]@row, 2)) / 60) + ([Task End Date]@row - [Downtime Start Date]@row) * 24) - (VALUE(LEFT([Downtime Start Time]@row, FIND(":", [Downtime Start Time]@row) - 1)) + VALUE(RIGHT([Downtime Start Time]@row, 2)) / 60), //insert formula used if downtime is blank//)

    The 1st IF says that if your downtimes both have values then respond with your equation. You'll need to complete the place holder at the end which is what to do if either downtime is blank.

    Help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Dan Crimmins
    Dan Crimmins ✭✭✭✭
    Options

    Hey Mark,


    Thanks for the reply, I am about to give it a try.


    For clarity where I say:


    Downtime Start Date from New Job

    Downtime Start Time from New Job


    These are 2 separate columns from:


    Downtime Start Date

    Downtime Start Time


    Thanks and I will let you know how I get along

  • Dan Crimmins
    Dan Crimmins ✭✭✭✭
    Options

    Hi,


    It's getting so close !!

    I adapted the formula adding in the formula to use when downtime is blank at the end (as indicated by the first reply) however now what happens in my result is:

    IF Downtime Start Date & Downtime Start time are blank the formula uses the values from Downtime Start Date from New Job & Downtime Start Time from New Job which is correct

    However if Downtime Start Date & Downtime Start Time BOTH have values in then I get #INCORRECT ARGUMENT whereas because they are both populated I want to use them instead of Downtime Start Date from New Job & Downtime Start Time from New Job

    If one of the other Downtime Start Date & Downtime Start Time has a value then the formula still works, however if they both have a value them it doesn't.


    Hope that makes sense !! I can rename the columns if it helps !

    Formula as it stands is :

    =IF(AND(ISDATE([downtime start date]@row), NOT(ISBLANK([downtime start time]@row))), IF((VALUE(LEFT([Task End Time]@row, FIND(":", [Task End Time]@row) - 1)) + VALUE(RIGHT([Task End Time]@row, 2)) / 60) + ([Task End Date]@row - [Downtime Start Date]@row) * 24) - (VALUE(LEFT([Downtime Start Time]@row, FIND(":", [Downtime Start Time]@row) - 1)) + VALUE(RIGHT([Downtime Start Time]@row, 2)) / 60),((VALUE(LEFT([Task End Time]@row, FIND(":", [Task End Time]@row) - 1)) + VALUE(RIGHT([Task End Time]@row, 2)) / 60) + ([Task End Date]@row - [Downtime Start Date from New Job]@row) * 24) - (VALUE(LEFT([Downtime Start Time from New Job]@row, FIND(":", [Downtime Start Time from New Job]@row) - 1)) + VALUE(RIGHT([Downtime Start Time from New Job]@row, 2)) / 60)

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    I included and extra IF. Try:

    =IF(AND(ISDATE([downtime start date]@row), NOT(ISBLANK([downtime start time]@row))), ((VALUE(LEFT([Task End Time]@row, FIND(":", [Task End Time]@row) - 1)) + VALUE(RIGHT([Task End Time]@row, 2)) / 60) + ([Task End Date]@row - [Downtime Start Date]@row) * 24) - (VALUE(LEFT([Downtime Start Time]@row, FIND(":", [Downtime Start Time]@row) - 1)) + VALUE(RIGHT([Downtime Start Time]@row, 2)) / 60),((VALUE(LEFT([Task End Time]@row, FIND(":", [Task End Time]@row) - 1)) + VALUE(RIGHT([Task End Time]@row, 2)) / 60) + ([Task End Date]@row - [Downtime Start Date from New Job]@row) * 24) - (VALUE(LEFT([Downtime Start Time from New Job]@row, FIND(":", [Downtime Start Time from New Job]@row) - 1)) + VALUE(RIGHT([Downtime Start Time from New Job]@row, 2)) / 60)

    Work now?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Dan Crimmins
    Dan Crimmins ✭✭✭✭
    Options

    That is fantastic Mark, thanks so much

    I am going to work my way through it and figure out just how it works so I will know for next time........It does work just as I needed it to and you have saved me a lot of time and head scratching so thanks again


    Dan

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Glad I could help Dan. Thank you for contributing to the Community. Please accept my answer to close the discussion. (I work for Vote Up and Insightful clicks.)

    Be well,

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!