Sheets: to_date formula to get date from timestamp

Question

I'm posting information from a form to google sheets.

After the insert (which adds the last row), I want to do some apps script post processing. Before I do the post processing I want to fill in a couple of blank cells which are not submitted by the form, including the DATE column which should be derived from the submitted TIMESTAMP column next to it. I'm trying to do this with the following formula:

=ArrayFormula(IF(ISBLANK($Q$2:$Q),"",to_date(Q2)))

But I'm getting a 'circular dependency error'

What am I doing wrong?

Answer 1

=ArrayFormula(to_date(IF(ISBLANK($Q$2:$Q),"",Q2:Q)))

Should do the trick. I don't understand how you are getting a circular dependency error (that happens when you are referencing the same column you have your formula in e.g. the formula above switching all Qs for Ps)

Answer 2

Slightly shorter:

=ARRAYFORMULA(if(Q2:Q="","",to_date(Q2:Q)))