I have responses in column D that are in HH:MM:SS format that I need to convert to seconds as the responses come in. I understand that doing an array in another column in Sheets is the proper solution, however, I can’t seem to figure out the syntax. Here is the formula not in an array: "=IF(D2="","",(sum(hour(D2)*3600,minute(D2)*60,second(D2))))". Can somebody help with how to make this formula into an ArrayFormula?
I tried a few different versions but they were not even close I imagine.
>Solution :
You may try this; the formula goes into row_1 (e.g. X1)
=vstack(
"Header",
map(indirect("D2:D"),lambda(Σ,if(Σ="",,sum(hour(Σ)*3600,minute(Σ)*60,second(Σ)))))
)