Grab only Dates from timestamp, automatically format once the new data is populated from a google form

Question

I want to grab only the dates from timestamps. Now I have this formula =ArrayFormula(if(len(A2:A),int(A2:A),IFERROR(1/0))) However, when the new date is entered, the dates becomes something like this 42338 then I have to format it everytime when a new data is entered.

This is what it looks like now. http://i.stack.imgur.com/y3zZn.png

I tried using =text(A2:A,"yyyy-mm-DD")), it solves my problem but my query in another sheet won't recognize this formate as dates.

Please help! Thank you

can you point the query in your other sheet towards the timestamp in column A, rather than the text you are displaying in column B? — Chris Hick
Hi, I dont know how I would write that for query. I want to group only dates within a certain range. Dont know how to do that if timestamps have time in it. Please let me know if you have insights on this. — Snow

Chris Hick Chris Hick · Accepted Answer · 2015-12-01T16:21:01

You could use an arrayformula within your query dataset to convert the dates to numbers.

This example sheet contains a couple of approaches using QUERY and SUMIFS: https://goo.gl/JXunQB

Grab only Dates from timestamp, automatically format once the new data is populated from a google form

1 Answers