Hi,
**During this question i shall be referring to cells within the spreadsheet attached**
N.b. I am using a VBA shortcut to insert the dates.
Basically i want to figure out the duration between 2 of the cells depending on whether one of them is filled or not. I want G5 to contain the difference between E5 and D5 UNLESS there is a date in F5 then i want the duration between F5 and D5.
I've tried the following code from my very basic knowledge and googling but it keeps coming up with an error.
=sum(IF((AND(ISBLANK(F5), NOT(ISBLANK(E5))), "=sum(E5-D5)", "=sum(F5-D5)")))
With error
"there's a problem with this formula. Not trying to type a formula? When the first character is an equal (=) or minus(-) sign, Excel thinks it's a formula:
-you type: =1+1, cell show: 2
To get around this, type an apostrophe ( ' ) first:
-you type: '=1+1, cell shows =1+1"
Any help will be greatly appreciated, a walk-through of why my formula isn't working and why yours does what also be appreciated.
Thank you in advance.
Ben
Hi Ben,
There's no need to use the equals sign or quotes in your formula, and you don't need the SUM() function either . Try this:
=IF(ISBLANK(D5), "", IF(ISBLANK(E5), "No Response Yet", (IF(ISBLANK(F5), E5-D5, F5-D5))))
This formula checks D5 for a value. If blank, then nothing is returned.
If D5 has a time, the next IF checks E5 for a value. If blank, the cell shows the statement, "No Response Yet".
If D5 and E5 have times, the next IF checks F5 for a value. If blank, the difference between E5 and D5 is shown, otherwise, the difference between F5 and D5 is shown.
Hope that helps!
Hi Ben
Looking at your question and spreadsheet I assume that you want the time elapsed between E5 and D5 if F5 is blank, otherwise you want the time elapsed between F5 and D5. The following formula will do that:
=IF(ISBLANK(F5),E5-D5,F5-D5)
The formula uses the ISBLANK function to test if cell F5 is blank. If that is true it calculates the difference between E5 and D5, otherwise (ie. F5 is not blank) it calculates the difference between F5 and D5.
I usually incorporate a couple of other tests just in case:
=IF(OR(ISBLANK(F5),LEN(F5)=0,F5=""),E5-D5,F5-D5)
This also uses the LEN function to test if the length of a cell is zero, and tests if the cell is a zero-length string.
See this for a discussion of the ISBLANK function.
You could also incorporate more logic to test if the contents of the cells are true date/times, but since you are entering the data with a VBA shortcut you should incorporate any necessary validation/formatting in that.
Hi Both,
Thank you both so much for the help. Both ways worked perfectly fine, i however opted to use Catherine's code due to the addition of the no response yet being added in (nice touch!).
I would have marked each as being the answer if that was the button i pressed, i pressed useful answer (Which they both are) but for some reason they've got that button set to make that the reply the answer of the question.
Thanks again,
Ben
