0

This causes he headaches every time I use ArrayFormulas in Sheets. It seems that you have to specify the exact length of the range, and can't just use for example X2:X like you can in any other range definition in a normal formula. Is there an easy solution for this?

My current formula is:

=ArrayFormula(DATEVALUE(G2:G700))

I would think I could just do:

=ArrayFormula(DATEVALUE(G2:G))

but it doesn't work. How can this result be achieved?

1
  • The latter formula should work just fine. It is unclear what the problem is. If you are not happy with the results your formula gives on rows 701 and on, please specify what you want to get when column G does not contain a datetime value but a blank, a text string, or a number. Jun 5 at 16:47

1 Answer 1

1

To make the formula return a blank on rows where column G is blank, use one of these patterns:

=arrayformula( if( isnumber(G2:G), datevalue(G2:G), iferror(1/0) ) )

=arrayformula( iferror( 1 / datevalue(G2:G) ^ -1 )

4
  • Holy crap those both totally work. Trying to get my head around what they're doing however, and failing. How does dividing 1 by 0 and seeing if it's an error (which it always will be), help? EDIT: It doesn't matter... can just be "" as well. The important part is testing to see if G is a number. Thanks!
    – JVC
    Jun 5 at 16:50
  • Not sure what you mean, it works fine. Doesn't need the final iferror however (first example). Just needs to test if G is a number or not. Thanks, all good.
    – JVC
    Jun 5 at 16:52
  • 1
    The zero-length text string "" is not a truly blank value. To get a truly blank value, use the iferror(1/0) pattern. Many spreadsheet functions, such as counta(), will not consider zero-length text strings blank, so the iferror(1/0) pattern is preferable in practically all use cases. Jun 5 at 16:56
  • Ahhh ok, I see the distinction. Good trick, thanks!
    – JVC
    Jun 5 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.