200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I am calculating time elapsed by subtracing one set of date/time from another, but when one of more of the cells for the date/time are blank, I want to return a blank in the time elapsed cell instead of the ####### that is currently populated. Below is an example of the formula that I am currently using.
=((N2+O2)-(D2+E2))
I have tried adding IFERROR several ways and have not figured out how to get this to work. Of course, I know that I also need to add ,"" somewhere along the line.
Welcome! :)
The problem is that dates and time in Excel can be tricky. You aren't actually getting an error. Format the cell to General and you will simply see a negative number.
Use a regular IF statement and check if the value is less than 0 and, if it is, output "" (nothing), but, if it's greater than 0, execute the formula; that's why the last section of the IF statement repeats your formula.
=IF(((N2+O2)-(D2+E2))<0,"",((N2+O2)-(D2+E2)))If you test the cells in question for 'blank' this may help...
=IF(OR(ISBLANK(N2),ISBLANK(2),ISBLANK(D2),ISBLANK(E2)),"one of the values is blank",(N2+O2)-(D2+E2))
My guess is that the latter cells you reference in your formula (D2 and/or E2) may be the ones you want to check if they are blank? if that is true, then you may need to ONLY check those cells for the blank condition, however, the above formula will ensure that ALL 4 cells in your formula (N2+O2)-(D2+E2) are not blank. you can modify the formula however you wish though.
Oh, by the way, this will put a label "one of the values is blank" if it is true, if you use Excel's F2 key for Edit - this can Highlight the cells that you are using and you can see which are blank. (this formula will NOT tell you which cell or cells are Blank!)