microsoft excelmicrosoft-excel-2010worksheet-function
I want to subtract two cells in excel but only if both cells have a value. More specifiaclly in my case the formula in cell C2 is currently =B2-A2 but I only want this to be calculated when B2 has a value or A2 has a value. I tried this formula
=IF(AND(B12<>"",A12<>""),B12-A12," ")
but after the 12th row, it started showing an error of #VALUE!
Place the following formula in cell E2 to achieve what you want:
=IF(ISBLANK(D2),"",D2-C2)
If you want both cells to have a value:
=IF(OR(ISBLANK(D2),ISBLANK(C2)),"",D2-C2)
I agree with Excellll; you're going to need VBA to convert the values in place to a string value.
This method won't result to text strings; but it might help you get the format you need.
Let's say these were my minutes:
Type 1440 into any blank cell. Select this cell and press Ctrl + C.
Select the range that contains the minutes.
Paste Special > Values > Divide
Click OK. Result:
Select the range of number again and press Ctrl + 1.
Go to Number > Custom
Type the format:
[h] "h" mm "m"
Result:
If you'd like to change them back to minutes:
1440 into any blank cell. Select this cell and press Ctrl + C.To get the number of hours in decimal form, use 60 instead of 1440. So for 330 (mins), you'll get 5.5 (hours).
Best Answer
You can use the
ISBLANKfunction:=IF(OR(ISBLANK(A2),ISBLANK(B2)),"",B2-A2)