HomeGoogle DocsSpreadsheetHow to Format Date, Time, and Number in Google Sheets Query

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

Published on

By using the Format clause, the last clause in the Query clause order, we can format date, time, and number column in Google Sheets Query.

If you aren’t a newbie to Query function in Google Sheets, you can simply follow this tutorial. Otherwise, you may please check my function guide. There you can find Query function in the list. Read that first.

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.

Introduction

Query formula offers limited formatting options involving number, date, and time only.

I thought a column containing Boolean TRUE or FALSE can also be formatted to text by using Google Sheets Query. But sadly it’s not working.

You can learn here the different formatting options available one by one. Then you can learn how to combine all these formattings in one single formula.

Let’s begin first with Date formatting in Query. For all the following examples, I’m using the below table.

Formatting Options in Query - Date, Time and Hour

It’s sample data showing a club’s membership status like the name of members, gender, joining date, subscription charge, allotted time, etc.

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 is. But the column C date would be formatted in the “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 the 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'")

Note:

Please remember that we are only changing the formatting of the dates. The underlying values in the formatted cells (column) will be dates. You should take care of this in the calculation.

If you want to change the date for calculation purposes, you should then use scalar functions. Below is one example.

You May Like: Use of Month Function in Query Formula in Google Sheets.

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

Must Read: How to Use the toDate Scalar Function in Google Sheets Query.

How to Format Time in Query in Google Sheets?

Similar to the Date formatting, you can format time too. There are three types of time formatting options that I could find in the Query function.

  1. Time to Hours.
  2. Time to Seconds.
  3. As far as I know, we can’t format time to minutes alone. Instead, we can opt for minutes plus seconds (Time to Minutes and Seconds)

Query Formula to Format a Time in “HH:MM:SS” format to HH only (Hours, Minute, Seconds to Hours only).

=QUERY(A1:G7,"SELECT * FORMAT G 'HH'")

Format a Time to MM:SS (minutes and seconds) 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 into any detail. You just apply these formulas in your sheet and see how it work.

Formula # 1:

=QUERY(A1:G7,"SELECT * FORMAT E '##,##0.00'")

Formula # 2:

=QUERY(A1:G7,"SELECT * FORMAT E '##,##0'")

How to Apply Multiple Formatting Options at a Time in Query?

You only need to separate the formatting using a comma that followed by the corresponding column identifier.

=QUERY(A1:G7,"SELECT * FORMAT C 'DD/MMM/YYYY', E '##,##0.00'")

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 click Here to find more. That’s all for now. Enjoy!

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

18 COMMENTS

  1. Having an issue where the format is always returning ‘1/1/1900’

    Column G is just a list of dates in dd/mm/yyyy format. Column I is just a list of numbers I want the average of by month.

    =QUERY({ArrayFormula(EOMONTH(G2:G,0)),I2:I},"select Month(Col1)+1,AVG(Col2) group by Month(Col1)+1 label Month(Col1)+1 'Month' format Month(Col1)+1 'MMM-YYYY', AVG(Col2) 'Average'",0)

    I know it’s something simple but I can’t get it to work.

    • Hi, Mike C,

      There are two issues.

      1. Month(Col1)+1 is not required since you use EOMONTH.
      2. Your following incorrect clause order.

      Try this formula.

      =ArrayFormula(QUERY({if(G2:G="",,EOMONTH(G2:G,0)),I2:I},"select Col1,AVG(Col2) where Col1 is not null group by Col1 label Col1 'Month', AVG(Col2) 'Average' format Col1 'MMM-YYYY'",0))

  2. I stumbled upon a problem would it be possible to add words to the number format: 1000sqm?

    To be more precise, any time I add FORMAT SUM(F) '0.00 "sqm" ', I have a VALUE error.

  3. I am wondering whether conditional formatting (such as a cell color) can be applied to column values rendered from a query. Thoughts?

  4. Hello,

    I have been reading your tutorial for quite some time and find it very useful for it to appoint where do I make mistake in my formula or logic or process whatever.

    Usually, I will keep on searching for the answer until I get it right but this one been troubling me for quite some time already.

    My problem is like this.

    I import data from another spreadsheet using Importrange and then use QUERY for a much more detailed column I desire to use. It seems that I can’t get the column to be formatted as I want it to be without going to the toolbar.

    I wish it to be formatted in the formula like the above tutorial but it seems that it can only work if I select all (*)

    Is it possible to format column by column in the query formula?

    Please help me here.

    • Hi, Azahari Saad,

      There is no such issue. Here is one example.

      =query(Orders, "Select A,B,C,D where B = 'Completed' AND C >= date '"&TEXT(C1,"yyyy-mm-dd")&"' FORMAT C 'DD-MMM-YYYY'")

      I could not enter my formula in your sheet because of below two reasons.

      1. Shared sheet is in VIEW mode.
      2. You have not explained the columns to format.

      I have copied part of your data, name it ‘Orders’, and tried my above formula without any error.

      When using the Format clause do follow the Clause order.

      • Thank you so much for pointing this out to me. I have tried it few times and after carefully reviewing the formula sequence and orders, I manage to achieve what I was searching for…

        Hope that you’ll be blessed always…

  5. Hello Prashanth,

    I have created a formula to extract the data from the import-range data. But I am unable to change the format of the date in the query function. When I double click the dates it does not show with the calendar on it, and that is not helping me at all for my other plans for this data. Could you please help and sort this out? Thank you.

    Here is the link.
    … removed by admin …

  6. Hello!

    Do you know if it’s possible to format the output format of date used in a pivot?

    =transpose(query(moisEnCours!E1:H;"select E, Sum(H) where H is not null and E='"&Q1&"' group by E pivot F order by E";1))

    Here F is returned as a US format 2019-1-16, I’d like it as European format 16/01/2019.

    Thanks!

LEAVE A REPLY

Please enter your comment!
Please enter your name here