Datetime Query format issue Google sheets

0
votes

I have two Sheets Daily and Monthly with 8 Cols and I have one dashboard. When I use query function for the dashboard the datetime in Col1 and Col5 are not coming out perfectly as time only shows 00:00:00. Please help I am attaching the Sheet link for reference feel free to make a copy and use.

Link -

https://docs.google.com/spreadsheets/d/1mSwnOq2Dh61HiS5tYg9vwSXkfiptoQxhS6stIqmEqNI/edit#gid=2089400460

Code -

=QUERY({FILTER('MONTHLY AND MISC'!A2:H,'MONTHLY AND MISC'!A2:A<>"");FILTER('DAILY TASK'!A2:H,'DAILY TASK'!A2:A<>"")},"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 where Col5 >= datetime '"&TEXT($A$2,"yyyy-mm-dd HH:mm:ss")&"' and Col5 <= datetime '"&TEXT($B$2,"yyyy-mm-dd HH:mm:ss")&"' ",0)

2
jquerydatetimegoogle-sheets

2 Answers

0
votes

Your numbers are coming over in that format because the daily task tab isn't a timestamp, it is a date. Dates converted to timestamps have the default value of 00:00:00 for hours, minutes, and seconds.

0
votes

add a new column (i.e. 'I') with

=arrayformula(timevalue(E2:E))

then try

=QUERY({'DAILY TASK'!A2:I;'MONTHLY AND MISC'!A2:I},"Select Col1,Col2,Col3,Col4,Col5,Col9,Col6,Col7,Col8 where Col5 >= DATETIME '"&TEXT($A$2,"yyyy-mm-dd HH:mm:ss")&"' and Col5 <= DATETIME '"&TEXT($B$2,"yyyy-mm-dd HH:mm:ss")&"' ",0)

you will get separately the date and the time