How to change Google Sheets date format and convert dates to numbers and text

Dates are an inevitable part of Google Sheets. And like many other concepts of spreadsheets, they require a bit of learning.

In this tutorial, you will find out how Google stores dates and how you can format them for your better convenience. Some date formats are offered to you by spreadsheets while others should be created from scratch. There are even a couple of handy functions for the task.

I also describe a couple of ways to convert your dates to numbers and text if necessary.

How Google Sheets formats dates

First things first: before any activities related to dates in spreadsheets, it's crucial to understand the core principles of how dates work.

For its internal database, Google Sheets stores all dates as integer numbers. Not sequences of a day, month, and year as we got used to seeing, but simple integers:

  • 1 for December 31, 1899
  • 2 for January 1, 1900
  • 102 for April 11, 1900 (100 days after January 1, 1900)
  • and so on.

Unlike Excel that cannot store dates as negative numbers, in Google, for dates prior to December 31, 1899, the numbers will be negative:

  • -1 for December 29, 1899
  • -2 for December 28, 1899
  • -102 for September 19, 1899
  • etc.

Regardless of how Google Sheets formats dates for you to see in cells, spreadsheets always store them as integers. It's an automatic Google Sheets date format that helps to treat dates correctly.

Tip. The same goes for time units – they are merely decimals for your table:

  • .00 for 12:00 AM
  • .50 for 12:00 PM
  • .125 for 3:00 AM
  • .573 for 1:45 PM
  • etc.

A date paired with time is kept as an integer with decimal places:

  • 31,528.058 is April 26, 1986, 1:23 AM
  • 43,679.813 is August 2, 2019, 7:30 PM

Change date format in Google Sheets to another locale

Another important thing to keep in mind is your spreadsheet locale.

The locale is what presets your Google Sheets date format based on your region. Thus, if you're currently in the US, 06-Aug-2019 will be put as 8/6/2019 in your sheet, while for the UK it'll be 6/8/2019.

To ensure the correct calculations, it's vital to have the correct locale set, especially if the file was created in another country:

  1. Go to File > Settings in the Google Sheets menu.
  2. Find Locale under the General tab and pick the desired location from the drop-down list: Change your spreadsheet locale to ensure the correct Google Sheets date format.

Tip. As a bonus, you can also specify your time zone here to record your file history in it.

Note. The locale doesn't change the language of your Sheets. However, the date formatting will be applied to the entire spreadsheet. Everyone who works with it will see the changes, no matter their place on the globe.

How to change date format in Google Sheets

If dates in your tables are formatted inconsistently or all you can see is strange sets of numbers instead, don't panic. You simply need to change the date format in your Google Sheets using built-in instruments.

Default Google Sheets date format

  1. Select all cells you'd like to format.
  2. Go to Format > Number in the spreadsheet menu and pick Date to see the date only or Date time to get both date and time in a cell: How to format date in Google Sheets.

The integers successfully turn into the format that you'll recognize at a glance. These are default Google Sheets date formats: Automatic format (integers) vs. date format.

Tip. You can find the same formats if you click on the 123 icon on the spreadsheet toolbar: Another way to change date format in Google Sheets.

Custom date formats

If you don't like how Google Sheets formats dates by default, I won't blame you. Luckily, there's plenty of room to improvise thanks to custom date formats.

You can access them from the same Google Sheets menu: Format > Number > Custom date and time: Google Sheets custom date and time formats.

You will see the window with lots of different custom date formats available. Whichever one you chose and apply, your dates will look the same: Apply custom formats from the list.

If you're still unhappy with the appearance of your dates, you can tailor your own custom date format:

  1. Select the cells you want to format.
  2. Go to Format > Number > Custom date and time.
  3. Place the cursor into the field at the top that contains the date units and delete everything with your Backspace or Delete keys: Remove the existing custom date format.
  4. Click the arrow to the right of the field and pick the unit you'd like to have first. Don't forget to type the separator afterwards.

    Repeat till all the necessary units are added (no worries, you'll be able to add or remove them later): Add as many units to your custom date format as you need.

  5. Notice that each unit has double arrows to its right. Click them and you'll be able to adjust the exact way to display the value.

    Here's what I can choose for Day: Choose how the day should look in your date format.

    This way, you can edit all values, insert additional and delete obsolete ones. You are free to separate the units with various characters including commas, slashes, and dashes.

  6. Once you're ready, click Apply.

Here's what format I've created and how my dates look now: Google Sheets custom date format anew.

QUERY function for Google Sheets to format dates

There's one more way to change date format in Google Sheets – with a formula, of course. Since this is not the first time of me showing QUERY to you, I'm starting to think of it as a real cure-all for spreadsheets. :)

I have an example table where I track the shipment of a few orders: My table with shipped orders.

I want to change the date format in column B. Here's my QUERY formula:

=QUERY(A1:C7,"select * format B 'd-mmm-yy (ddd)'")

  • first, I specify the range of my entire table – A1:C7
  • then I'm asking the formula to return all columns – select *
  • and at the same time re-format column B the way I put into the formula – format B 'd-mmm-yy (ddd)'

The formula works like a charm. It returns my entire table and changes date format in column B: Change date format in Google Sheets with the QUERY function.

As you may have noticed, to change the date format via the formula, I used special codes that represent different looks of days, months, and years. If you're not familiar with them, here's a list of these codes for dates:

Code Description Example
d Day without a leading zero for 1-9 7
dd Day with a leading zero for 1-9 07
ddd Day as an abbreviation Wed
dddd Day as a full name Wednesday
m
(if not preceded or followed by
hours or seconds)		 Month without a leading zero 8
mm
(if not preceded or followed by
hours or seconds)		 Month with a leading zero 08
mmm Month as an abbreviation Aug
mmmm Month as a full name August
mmmmm First letter of the month A
y
or
yy		 Two digit year 19
yyy
or
yyyy		 Full numeric year 2019

Tip. If you'd like to supply your date format with time as well, you need to add codes for time units. You will find the full list of time codes in this guide.

Using these codes, you can format the dates in so many ways:

  • Get the hold of the year, month, or day only:

    =QUERY(A1:C7,"select * format B 'yyyy'") Use QUERY to return the year only.

  • Return the day, month, and the day of the week:

    =QUERY(A1:C7,"select * format B 'dd mmmm, dddd'") Convert date to day of week, day and month.

By the way, what date format have you got used to? :)

Google Sheets: convert date to number

In case you need to see numbers instead of dates, one of the methods below will be useful.

Convert date to number by changing the format

  1. Select those cells with dates that you want to convert to numbers.
  2. Go to Format > Number and this time pick Number among other options.
  3. Voila – all selected dates have turned into numbers that represent them: Convert Date format to Number format.

DATEVALUE function for Google Sheets

Another way for Google Sheets to convert date to number is by using the DATEVALUE function:

=DATEVALUE(date_string)

where date_string represents any date in the known for spreadsheets format. The date should be put in double-quotes.

For example, I want to convert August 10, 2023 to a number. All the formulas below will return the same result: 45148.

=DATEVALUE("August 10, 2023")
=DATEVALUE("2023-8-10")
=DATEVALUE("8/10/2023") DATEVALUE formulaswith different format returns the same number for the same date.

Tip. If you're not sure whether Google Sheets understands the format you're about to enter, try typing the date into another cell first. If the date is recognized, it'll be aligned to the right.

You can also fill your cells with dates in one column, and then reference them in your formulas in another column:

=DATEVALUE(A2)

Google Sheets: convert date to text

Converting dates to text in spreadsheets is the task for the TEXT function:

=TEXT(number,format)
  • number – regardless of what number, date, or time you give to the function, it will return it as text.
  • format – the text will be formatted the way you specify in the formula.

    Tip. To set the format correctly, use the same codes as you did for the QUERY function.

The real-data formula may look like this:

=TEXT("2/7/2023","YYYY-MM-DD")

Here's how I converted my date – 2/7/2023 - to text and changed the format at the same time: Google Sheets: convert dates to text and change the format.

Google Sheets: convert text to date

Sometimes your dates may appear in a way that Google Sheets doesn't understand at all. And it doesn't really matter whether they are imported or you just fancy a certain view: Some custom formats that Google Sheets doesn't understand at once.

Unless you specify a custom format for each cell or change the locale of your spreadsheet, these dates will be formatted as text by default. You won't be able to use them in formulas or other calculations.

But here's the easiest solution for your Google Sheets: convert text to dates using the Power Tools add-on. It's literally a single radio button among other convert tools. Power Tools: convert text to dates radio button.


Google Workspace Marketplace badge

The tool recognizes all these custom formats in your Google Sheets and converts text to date so all cells become consistent and can be used for further reference: Google Sheets: Convert text to date using the Power Tools add-on.

This is it! I hope by now you know how to change date format in Google Sheets and convert dates to numbers or text. Feel free to share other cool ways in the comments section below. ;)

Table of contents

207 comments

  1. Wow this is amazing!

  2. Hi, How can I extract just the date here - Jul 18, 2022, 6:45:03 PM

  3. Hi there!

    How can change date format from 5/23/22 0:00 to 2022-05-23 0:00?

  4. I have an query function that is combining all the date from 11 different tabs in google, but I can't seem to get my function right where it copies the dates from Column M.

    =QUERY({Wagram!A4:AJ;SEARCH!A4:AJ;SHS!A4:AJ;Carver!A4:AJ;SycamorePrimary!A4:AJ;SycamoreElementary!A4:AJ;SpringHill!A4:AJ;SouthJohnson!A4:AJ;LaurelHill!A4:AJ;ABGibson!A4:AJ;Shaw!A4:AJ},"select * where Col13 is not null",-1)

    M has a function in it in each of the 11 tabs where it adds 90 days to a different date on the same spreadsheet. It has something to do with the function not picking up the date or serial number...

    All my dates in M will be less than 6/30/2023. I hope this helps.

    • Hello Yvette,

      At the glance, your formula looks correct.

      For me to be able to help you better, please share your spreadsheet with us: support@apps4gs.com.
      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved. Or you can replace any important information with some irrelevant data, just keep the format.

      I'll look into the problem.

  5. Hi,
    I have a format problem, concerning dates, I want dates in dd/mm/yyyy format, and I don't want useless 0 like 01/01/2022, but i want 1/1/2022. But some dates remain with useless 0 and don't changed into 2022 for example :

    18/09/14 20/09/14 18/09/14 20/09/14
    14/10/14 25/09/19 14/10/14 25/09/19
    14/10/14 25/09/19 14/10/14 25/09/19
    14/10/14 25/09/19 14/10/14 25/09/19
    1/1/1980 7/7/2015 1/1/1980 7/7/2015
    2/12/2019 27/01/21 1/1/2020 17/01/21
    20/06/18 27/01/22 26/01/22 27/01/22
    10/9/2021 12/10/2021 10/9/2021 10/9/2021
    21/09/17 8/6/2020 21/09/17 8/6/2020
    8/11/2019 21/01/20 11/12/2019 11/1/2020
    24/05/18 8/6/2022 24/10/18 10/7/2019

    Thanks for your answer.

      • As i am trying to use countifs formula for eg:- column C has filled with dark and column b with date , so I want to use this formula in a way if i give a date range for 15th jan to 20th jan how many count of dark is there while trying a formula in excel it was working but not in google sheet as there was some issue with date format while trying to change the date format for few cells it got changed but for few it didnt how to resolve these both issues

        • Hello Satish,

          I'm sorry but your task is not clear. For me to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data with the formula that doesn't work (2) the result you expect to get. The result sheet is of great importance as it gives us a better understanding than any text description.

          Please shorten the tables to 10-20 rows.

          Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

          I'll look into your task and try to help.

  6. I have a problem.
    When i enter 03/06/2022, it takes it as 3rd month which is march and date as 6. However, i want it to be 3 day of June.

    Please see if you can help.
    Thanking you
    Yash

      • Hello,
        I'm having the same question.
        I've imported .csv data from another locale (USA) to European locale.
        The sheets does not recognize the data as dates (when the day is bigger than 12)
        How can I rewrite to "my locale"?
        Custom Date & Time doesn't help/work

        Thank you & regards,
        Jan

  7. Hello!
    I am using date-time format(dd,mm,yyy hh:mm:ss) and I would like to plot the date on one axis in the time in the other axis.

    I think it is not possible and for this reason, I would like to separate the date and the time into two columns: in one the date, and in another one the time. Is there a query that can be used?

  8. I have a spreadsheet with dates expressed as dd/mm/yyyy but one individual cell incorrectly formats the output to mm/dd/yyyy and no matter how many times I've tried reformatting it to the correct format it won't display in the correct manner. Cells above and below display correctly, and reformatting all cells in the column also failed to correct this one cell. I've tried deleting the contents of the cell, removing formatting from the cell, copying formatting from other cells to no avail. While I can look at the data entry bar and see the correct date it doesn't display correctly in the cell. Any suggestions please?

      • Thanks Natalia. The locale was certainly wrong and I have fixed that. Unfortunately some cells despite changing the locale and re-formatting the cells, refused to show the correct (Australian) date settings with the data already entered. Luckily the spreadsheet was small so I manually re-entered the dates and they came out correct. I will be wise to the locale issue in future. Thanks again.

  9. Hello!
    My data exports in format "Mon Feb 21 10:58:16 UTC 2022" but I need to change this to some standard format such as "dd-mm-yyyy" (I don't care about the other exported info). Is there a way to do it with a formula? Or how else?
    Thank you!

    • Hello Mike,

      Assuming the date is in A2, try this formula:
      =DATEVALUE(MID(B5,5,6)&" "&RIGHT(B5,4))

      Don't forget to apply the Date format to a cell with this formula.

      • I changed the B5s in your formula to A2 and it works! Thank you, you are a wizard! Have an amazing day

  10. I have my date column as YR:MO:DY HR:MN:SE
    which is what I need, but my 'seconds' in that format have a decimal and additional digits, so the entry is longer that what is needed (only 2 digit seconds). Can I remove the decimal and all digits to the right via automated method which would leave only 2 digits for second data, or is that type of request not supported? (removing digits to the right of the decimal would still need to leave those same digits to the left of the decimal) Thanks.

      • format current: 2021/12/29 23:20:45.71994700:00
        format desired: 06/14/2017 20:57:35

        I am not sure how to switch the date format to MM/DD/YR within the context of the time data. And need to remove everything after and including the decimal for the seconds. Thanks.

        • Thank you for the examples, Shaun.

          If I get it correctly, you can first split your current record by a period using any method described here. This will return 2021/12/29 23:20:45 into one of the columns (let's suppose column B).

          Then you'll just need to use one the ways described in the blog post above to transform the format. For example: =QUERY(B2,"select * format B 'mm/dd/yyy h:m:s'")

          • Thanks, that was very helpful. One last question - I can only have 8 decimal places max. One of my columns is current 9 places. How do I delete only the 9th place to the right of the decimal? Example: 5.527243528

  11. Hi there,

    Apologies if you've answered this a million times. I was trying to figure out how to make the cell following the one that I edited automatically update to the preceding date. So if I type 2/23, the next cell updates to 2/24 and so on. It seems like this is an automatic feature but it's not working for me. I do have the first cell entered as the word "Date" and then the next cell entered as Sunday, Feb,20, then I'd like the next cells to automatically update the dates.

    I read about the custom date and time feature and did that. Also, I'm being picky here but I don't know why the date reads "Sunday, Feb 20," I wanted it to get rid of that last comma. Thanks so much!!

  12. Good day! Please help me figure out this Google sheet formula where i am getting a decimal numbers instead of date format from other sheet. I am using the =importrange(datevalue(sheetname!entire column name:entire column name&2/5/2022 8:50:00)) this formula "unique(sheetname!column name:column name) is working when it isn't joined in many formulas. Please let me know how to fix this problem. Thank you.

    • Good day, Jason!

      IMPORTRANGE requires a spreadsheet_url as its first argument, please see the details here.
      I'd also advise you to check the Number format applied to a column where your formula returns the data.

  13. When I’m using the keys to get current date and time. It gives the past date as xx/xx/1899. Please help me to fix this issue.

  14. Hello,
    I keep getting an error when i run this query;

    =QUERY(A2:N506,"select * format B,'d-mmm-yy (ddd)'")

    I have tried it with different sheets and the result is the same.
    The error is #VALUE!

      • Thank you, that was my error, now sorted.

  15. I keep getting this error when i run this query function

    =QUERY(A2:N506,"select * format B,'d-mmm-yy (ddd)'").

    I have tried it on different sheets and the result is the same .
    #VALUE!

  16. Hello!

    I've been ripping my hair out in regards to how Google Spreadsheet says it has Swedish formatting regarding to dates etc. but when I enter 20/1 (20:th of January) it flips it to 1/20, which is not how short dates are written in Swedish.

    The most common way to write dates are d/m-yy and that format is not recognised as a date format at all. Depending on importance we do use dd.mm.yyyy or yyyy-mm-dd as well and those do work.

    Is there a way to change this so that it recognises the dates as it should? It's really bothersome that the most common way to write something is regarded as unrecognisable...

      • Hi, Natalia!

        Thank you for your reply!

        What you suggest only works when writing the whole date in the cell and then tell it to show something else. If I copy the format by using the formatting tool (or manually apply the custom date format) it still flips it to 1/20 instead of 20/1.

        I am trying to make it easier to write the dates short and still use them as real dates, so the point is basically to rid the automatic formatting when entering new data in a cell as it is an error in regards to the regional settings. I have sent Google feedback on this issue several times and I'm trying to find a solution since it doesn't seem to be in their interest to fix it.

        • Hi Kristofer,

          Until Google Sheets changes the standard format for your region (which I wouldn't expect soon since it looks like they currently use the international standards. You can look up 'ISO 8601' on the Web to find out more), I'm afraid the suggested workaround with a full date is the only way to get the desired result (except for Apps Script, of course).

          I wish I could help you better.

          • Hi!

            Then I thank you for your time and effort; much appreciated!

  17. is there a way to convert yyyy-mm-ddT00:00:00.000Z format to a standard dd/mm/yyyy?

  18. I need to remove the timestamp and convert date to show only Month & Year, mmm-yyyy
    example - 28/11/2021 23:32:09 format to Nov-2021

    Is there a way to do this in one formula and keeping Nov-2021 in date format not text?

  19. From importrange I am importing data. It's having date field in dd/mm/yyyy (05/08/2021) format. After successful import I am able to check the date showing correctly (05/08/2021). But the thing is here it's showing month as 05 or May instead of 08 or Aug.

  20. A little correction:

    Instead of 102 for November 4, 1900 (100 days after January 1, 1900)

    the correct date is 4/11/1900

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)