Adapt formula to use different cells if original source is blank
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

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.

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

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.

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

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)

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.

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

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
Categories
Check out the Formula Handbook template!