How to do SUM vertical SPLIT to receive the value of a string in the format datetime "YYYY/MM-CodePudding

My strings are in this format:

COLUMN Z

2022/03/11 - 16:36:01
2022/03/11 - 16:29:41
2022/03/11 - 16:34:21

I want to convert them to VALUE() to find out if they are datetime greater or less than NOW(), if the values in the column were formatted as datetime, I could do it like this:

=ARRAYFORMULA(VALUE(Z1:Z)<VALUE(NOW()))

But they're not, so to convert this type of string, the method I use is:

COLUMN AA

=SUM(SPLIT(Z1," - "))<VALUE(NOW())
=SUM(SPLIT(Z2," - "))<VALUE(NOW())
=SUM(SPLIT(Z3," - "))<VALUE(NOW())

But I couldn't make SUM accept ARRAYFORMULA vertically, SPLIT accepts but SUM doesn't.

My failed attempt was:

=ARRAYFORMULA(SUM(SPLIT(Z1:Z," - "))<VALUE(NOW()))

How should I proceed?

CodePudding user response：

A parallel way to SUM SPLIT that I found for strings of fixed lengths is to use LEFT RIGHT.

LEFT to date value
RIGHT to time value

in my case I solved it this formula:

=ARRAYFORMULA(IFERROR(LEFT(Registros!Z1:Z,10) RIGHT(Registros!Z1:Z,8))<NOW())

CodePudding user response：

You can use MMult like this:

=ArrayFormula(if(Z1:Z="",,MMult(--iferror(split(Z1:Z,"-")),{1;1})<now()))