If or time calc help
I just took a basics excel class and some of the things excel can do that I have never used. I have several excel sheets that I have been modifying to include some automation successfully.
I have a sheet with a time calculation that I only want answered if it meets my requirements.
To calculate time past midnight I am using =MOD(b1-a1,1), this works well as long as A1 and B1 are filled. If are both are empty i get 0:00 but if only b1 is filled then I get b1 as the answer.
I tried
=IF(OR(A1=" ",B1=" ")," ",MOD(B1-A1,1))
Two problems with this you can't have a blank in a formula cell and I still get B1 as an answer if A1 is empty. I have tried entering text for the true answer "na" and B1 still is the answer. I am sure the solution is something simple but I have not found it yet.
I attached the file I am working on, if anyone can help I would much appreciate it.
Thanks
Rody
| Attachment | Size |
|---|---|
| updownlist.xlsx | 10.99 KB |
Take the spaces out
Try taking the spaces out between the double quotes.
Like this:
=IF(OR(I8="",H8=""),"",MOD(I8-H8,1))
Not like this:
=IF(OR(I8=" ",H8=" ")," ",MOD(I8-H8,1))
thanks a lot that fixed it...
thanks a lot that fixed it... its always something simple.
I was talking to a friend last night and he suggested using a nested IF instead of OR and no spaces between the double quotes.
=IF(I8="","",IF(H8="","",MOD(I8-H8,1)))
After finding out that this worked I never went back to check my old formula.
thanks again.
rody
Don't see the point on a
Don't see the point on a nested if statement when you can use the OR variable. Just remember that when you put a space between the quotation marks excel is not reading it as a blank but as the character for space, hence always using "" instead of " " to represent an empty cell.