Formatting Date, Time, and Numbers in Google Sheets Query

Using the FORMAT clause, which is the last in the Query clause order, we can format date, time, and number columns in Google Sheets Query.

The QUERY function retains the formatting of dates, times, and numbers from the source data, but the FORMAT clause allows you to alter that formatting.

This means you can change the date format from one format to another, such as YYYY-MM-DD to MM/DD/YYYY, and switch the time format from 24-hour to 12-hour and vice versa. The function supports formatting patterns defined by the International Components for Unicode (ICU).

Let’s start with date formatting in Query. For all the following examples, I’ll use the table below.

Sample data for formatting date, time, and numbers in QUERY function

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

How to Format Dates in the QUERY Function in Google Sheets

In our sample data, column C contains dates in DD/MM/YYYY format. The following formula converts those dates to YYYY-MM-DD:

=QUERY(A1:G7,"SELECT * FORMAT C 'YYYY-MM-DD' ", 1)

You can experiment with different date formats in this column.

For instance, you can use the FORMAT clause in the QUERY function to return only the day (number or text), month (number or text), year, etc., from a column that contains dates.

To format the date to year:

=QUERY(A1:G7,"SELECT * FORMAT C 'YYYY' ", 1)

To format the date to month:

=QUERY(A1:G7,"SELECT * FORMAT C 'MM' ", 1)
=QUERY(A1:G7,"SELECT * FORMAT C 'MMM' ", 1)

The first formula returns the month number, whereas the second one returns the month name in abbreviated form.

To get the day from a date:

=QUERY(A1:G7,"SELECT * FORMAT C  'DD' ", 1)
=QUERY(A1:G7,"SELECT * FORMAT C 'DDD' ", 1)

The first formula returns the day as a number, whereas the second formula returns the day of the week.

Note:

Please remember that we are only changing the formatting of the dates. The underlying values in the formatted cells (column) will still be dates.

How to Format Time in the QUERY Function in Google Sheets

One of the most common time formatting tasks is converting time from a 12-hour format to a 24-hour format or vice versa.

In our sample data, we have time entries in column G in the HH:MM:SS format (24-hour format).

See the following formulas and what they return:

=QUERY(A1:G7,"SELECT * FORMAT G 'HH:MM:SS AM/PM' ", 1) // 12-hour format with seconds
=QUERY(A1:G7,"SELECT * FORMAT G 'HH:MM AM/PM' ", 1) / 12-hour format without seconds

If your time entries are already formatted in a 12-hour format, you can use the following formula to convert them to a 24-hour format in Query:

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

In addition, you can format time in the QUERY function to display only the hours as follows:

=QUERY(A1:G7,"SELECT * FORMAT G 'HH' ", 1) // format to hours

How to Format Numbers in the QUERY Function in Google Sheets

The FORMAT clause supports formatting numbers as well in Query. Here are a couple of examples.

Decimal Format:

=QUERY(A1:G7,"SELECT * FORMAT E '0.00' ", 1) // fixed decimal places
=QUERY(A1:G7,"SELECT * FORMAT E '0.###' ", 1) // floating decimal places
=QUERY(A1:G7,"SELECT * FORMAT E '#,##0.00' ", 1) // thousand separator

Currency Format:

In all the examples above, you can prefix a currency sign such as $ (USD), € (EUR), £ (GBP), ¥ (JPY), ₹ (INR), etc.

Example:

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

Additional Note

When you want to format more than one column, separate each pattern property with a comma. Here is one example:

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

Resources

18 COMMENTS

waqar ahmad October 9, 2023 At 3:46 pm

I need your help! I want a query formula with time and date to automatically remove data after 8am.

- Prashanth October 9, 2023 At 7:34 pm
  
  Hi waqar ahmad,
  
  Please check this out: How to Use DateTime in Query in Google Sheets.
  
Mike C December 29, 2021 At 9:04 pm

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.

- Prashanth January 1, 2022 At 3:19 pm
  
  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))
  
Dawid Iwanczyk December 20, 2021 At 6:45 pm

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.

- Prashanth December 21, 2021 At 10:45 am
  
  Hi, Dawid Iwanczyk,
  
  It won’t work.
  
  As far as I know, the Query in Sheets supports the patterns defined by the International Components for Unicode (ICU).
  
Ken October 1, 2021 At 7:00 pm

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

- Prashanth October 2, 2021 At 10:47 am
  
  Hi, Ken,
  
  Please leave more details or a sample sheet.
  
  Thanks.
  
Azahari Saad February 21, 2021 At 7:07 pm

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.

- Prashanth February 22, 2021 At 9:33 am
  
  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.
  
  - Azahari Saad February 22, 2021 At 6:41 pm
    
    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…
    
Zeeshan November 28, 2020 At 1:37 am

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 …

- Prashanth November 28, 2020 At 11:07 am
  
  Hi, Zeeshan,
  
  I am unable to test anything on your sheet as it’s protected.
  
- Prashanth November 28, 2020 At 1:21 pm
  
  Hi, Zeeshan,
  
  It’s because the column B in your source contains some text values, which causes issues in Query. The query is famous for behaving unpredictably in a column with the data type called MIXED TYPE.
  
  So I have included a Filter with the Query ‘Data’ and presented the same in your sheet in the tab named “kvp”.
  
  - Zeeshan November 30, 2020 At 11:07 pm
    
    Thank you so much from resolving my issue. You are absolutely a genius.
    
COSTE Lionel January 23, 2019 At 11:46 pm

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!

- Prashanth January 24, 2019 At 7:59 am
  
  Hi, COSTE Lionel,
  
  I think this tutorial can help you!
  
  How to Format Query Pivot Header Row in Google Sheets
  
  Cheers!
  
Sebastian December 19, 2018 At 6:46 pm

Great!!! Thank you very much!!