29

This is what I have thus far:

=if(ISBLANK(A:A), (A:A+TIME(2, 0, 0)), "")

I'd like the script to add 2 hours to A:A or just remain blank if there is no data inside A:A

Any ideas would be greatly appreciated. Thank you :)

Chips147
  • 595
  • 3
  • 6
  • 12

4 Answers4

66

If you want to end up with a truly 'blank' value, you can use the expression IFERROR(0/0). This is different from an empty string which is what you get when you use "". A cell with an empty string will not test true using ISBLANK(), but IFERROR(0/0) will.

For example:

=IF(ISBLANK(A1),IFERROR(0/0),"not blank")

How this works: The IFERROR function takes two arguments. If the first argument is a value, IFERROR returns that value. If the first argument returns an error, IFERROR returns its second argument - but if there is no second argument, it returns blank. We use the expression 0/0 to deliberately force an error by dividing by zero, and because there's no second argument IFERROR returns blank.

Ian Viney
  • 811
  • 6
  • 5
  • 49
    Just realized that the `IF` function returns a blank value if you leave out one of its arguments, so you don't need the `IFERROR(0/0)` trick. For example, `=IF(ISBLANK(A1),,"not blank")` will work just as well! – Ian Viney Nov 26 '17 at 20:16
  • 1
    Google's [support documentation](https://support.google.com/docs/answer/3093304) on IFERROR says, "Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent." The [documentation](https://support.google.com/docs/answer/3093364) on IF isn't quite so explicit, but it does say, "value_if_false - [ OPTIONAL - blank by default ]". – Ian Viney Jan 31 '19 at 00:29
  • 1
    The [documentation](https://support.google.com/docs/answer/7014145) on IFS doesn't mention it, but if you omit a value parameter it does return a blank value. – Ian Viney Jan 31 '19 at 00:39
  • 1
    Majorly helpful comment! It should be noted that this is NOT the behavior Excel uses by default. – Zediiiii Feb 08 '19 at 06:33
  • 1
    Even if `iferror` is better documented than `if`, using `if` feels less hacky than forcing an error. – crantok Feb 02 '21 at 16:06
  • In case blank is needed in other contexts, a short way I discovered is `IF(,,)` with all three arguments left out! Incidentally, blank conditions appear to be treated as false, so for example `IF(,1,2)=2` is true. – Moh Jun 21 '21 at 22:59
  • A word of caution, if you want `IF` to return blank if it evaluates the expression to FALSE, `=IF(A:A > 0, A:A,)` will give you a blank. If you leave the comma off after the second expression, then FALSE will be returned, e.g., `=IF(A:A > 0, A:A)` – Fauxcuss Sep 11 '21 at 17:41
1

First, in if(condition,value_if_true,value_if_false) you have value_if_true and value_if_false reversed. So the first correction is

=if(ISBLANK(A:A), "", (A:A+TIME(2, 0, 0)))

Secondly, this will only fill one row. But we can assume that you want all rows, so you can wrap it with array formula:

=arrayformula(  if(ISBLANK(A:A), "", (A:A+TIME(2, 0, 0)))  )

Thirdly, we may also assume row1 is the header, so you want to skip that, so you can put the function in, say, B2 instead of B1, and the range needs to be changed from A:A to A2:A. I don't think A2:A range format is supported in Excel, but it's very useful in Google Spreadsheet.

daniel
  • 638
  • 4
  • 14
  • Ended up with this, then just dragged the formula down :) `=arrayformula(if(ISBLANK(A2),"",A2+TIME(2, 0, 0)))` – Chips147 Feb 26 '16 at 14:11
  • your `if` function contains no array parameter, so `arrayformula` is not needed. If the `if` function contains array parameter, `arrayformula` would automatically fill the rows below without the need to copy the formula down. – daniel Feb 26 '16 at 14:39
  • A2:A range format is definitely supported in Google Sheets, though it may not have been when this was posted. – Zediiiii Jan 29 '19 at 22:18
0

You need to add arrayformula around it - and swap where your empty string is - without seeing the data essentially what you want is:

=arrayformula(if(ISBLANK(A:A),"",A:A+TIME(2, 0, 0)))
Aurielle Perlmann
  • 5,323
  • 1
  • 15
  • 26
  • Ended up with this, then just dragged the formula down :) `=arrayformula(if(ISBLANK(A2),"",A2+TIME(2, 0, 0)))` – Chips147 Feb 26 '16 at 14:09
  • 1
    the reason you use array formula is so that you dont have to drag it down and you could also put it in B1, and alter it slightly to: =arrayformula(if(ISBLANK(A2:A),"",A2:A+TIME(2, 0, 0))) – Aurielle Perlmann Feb 26 '16 at 14:14
  • 1
    This does leave the cell BLANK, it puts an empty string into the cell so if you tested on the cell elsewhere, `ISBLANK(A2:A)` would be FALSE, and `ISTEXT(A2:A)` would be TRUE. If you really want it to be BLANK, go with `=ARRAYFORMULA(IF(ISBLANK(A2:A),,A2:A+TIME(2, 0, 0)))` – Fauxcuss Sep 11 '21 at 17:46
  • @Monkey47 is correct and this should not be the "correct" answer even if it helped OP solve the problem (or if it was previously correct but is no longer). Ian Viney's answer is "Correct" as far as the solution of getting a BLANK cell – Cfomodz Mar 14 '22 at 22:05
0

The solution I eventually was looking for was actually a slight modification of those above. I wanted the source cell to return an empty cell when the cell I was targeting was blank, otherwise I wanted it to return the value of the targeted cell (which was a boolean value):

=IFERROR(IF(ISBLANK('1578'!$B14:C14), , '1578'!$B14:C14), "")

/** Breakdown
 *
 * // In my use case the target had three potential values: empty (undefined), TRUE, or FALSE
 * '1578'!$B14:C14
 *
 * // Returns true if target cell is blank
 * ISBLANK('1578'!$B14:C14)
 *
 * // If target cell is blank, return nothing, else return value from target cell
 * IF(ISBLANK('1578'!$B14:C14), , '1578'!$B14:C14)
 *
 * // Return first value if no error (accounts for undefined target cell value) else return blank cell
 * IFERROR(IF(ISBLANK('1578'!$B14:C14), , '1578'!$B14:C14), "")
 */
Gavin Hughes
  • 21
  • 1
  • 6