How to Format Date, Time, and Number in Google Sheets Query

0
79
How to Format Date, Time, and Number in Google Sheets Query

If you aren’t a newbie to Query function in Google Sheets, you can simply follow this tutorial. Otherwise check my function guide. There you can find Query function in the list. Here I’m only detailing how to format date, time, and number in Google Sheets Query. But in between I would be sharing several other tips and tricks related to Query Function in the form of links to my earlier interesting tutorials.

How to Format Date, Time, and Number in Google Sheets Query Formula

Query formula offers limited formatting options involving number, date and time only. I thought a column containing Boolean TRUE or FALSE can also format to text by using Google Sheets Query. But sadly it’s not working.

We can learn the different formatting options available one by one. Then you can learn how to combine all these formatting in one single formula. Let’s begin first with Date formatting in Query.

For all the following examples, I’m using the below table as a base. It’s a sample data showing a club’s membership status like name of members, gender, joining date, subscription charge, allotted time etc.

Formatting Options in Query - Date, Time and Hour

How to Format Date in Query Function in Google Sheets?

Formula:

=query(A1:G7,”Select * format C ‘dd-mmm-yyyy'”)

This Google Sheets Query formula would return the above table as it’s. But the column C date would be formatted in dd-mmm-yyyy format. You can play around with different date formatting in this column.

I mean, you can use the formatting clause in Query to return only date, month, year etc. from a column that containing date. See the below formula which would only return the year in column C.

=query(A1:G7,”Select * format C ‘yyyy'”)

You can replace year with month or day as below.

Month
=query(A1:G7,”Select * format C ‘mm'”)
=query(A1:G7,”Select * format C ‘mmm'”)

Day
=query(A1:G7,”Select * format C ‘dd'”)
=query(A1:G7,”Select * format C ‘ddd'”)

In the first Para I’ve assured you I would be giving some additional tips related to Query. Here is one. You can remove time form time stamp in Google Sheets using Query. Follow the below link there I’ve included related tips.

Link: Lookup Dates and Return Currency Rates in an Array in Google Sheets

Note: Please remember that we are only changing the formatting of the values. You should take care of this in calculation. If you want to change date for calculation purposes, you should then use scalar functions. Below is one example.

Similar: Use of Month Function in Query Formula in Google Sheets

How to Format Time in Query in Google Sheets?

Similar to Date formatting, you can format time too. There are two time formatting options I could find. One is to Hour and the second one is Seconds. You can’t format a time to Minute alone. Instead you can opt minute plus second.

Format a Time in HH:MM:SS format to HH only (Hours, Minute, Seconds to Hours only)

=query(A1:G7,”Select * format G ‘MM:SS'”)

Format a Time to SS (seconds) only.

=query(A1:G7,”Select * format G ‘SS'”)

How to Format Number in Query in Google Sheets?

See two example formulas. I am not going in to any detail. You just apply it on your sheet and see how it work.

=query(A1:G7,”Select * format E ‘##,##0.00′”)
=query(A1:G7,”Select * format E ‘##,##0″)

How to apply multiple formatting options at a time?

=query(A1:G7,”Select * format C ‘DD/MMM/YYYY’, E ‘##,##0.00′”)

You only need to separate the formatting using a Comma. That’s all about how to format date, time, and number in Google Sheets Query.

Never Miss the Below Query Function Tutorials

1. QUERY Function in Age Analysis

2. Google Sheets Query Hidden Row Handling with Virtual Helper Column

3. How to Use QUERY Function Similar to Pivot Table in Google Sheets

There are plenty of tutorials on this page related to Query. You can follow Here to find more. That’s all for now. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here