Adapt formula to use different cells if original source is blank

Dan Crimmins

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

Mark Cronk

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

Mark Cronk

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

Mark Cronk

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

Dan Crimmins

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

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

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

Dan Crimmins

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

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