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. I am auto importing some data (with the IMPORTRANGE function) into a spreadsheet that contains a column of dates in this format - 28/06/2012 13:02:28 - but the new imported data into the same column is in this format - 2021-09-28T08:52:17.436Z - which I need to automatically change on import to - 28/09/2021 08:52:17 - Could you help with how/if that is possible? Thanks in advance.

    • Hello Simon,

      I'm afraid you'll need to convert that column afterwards separately using a formula like this:
      =SUM(SPLIT(A2,"TZ"))

  2. I have a column with BOTH dmmyyyy (one "d" when the day is 1-9, ex: 1092021 represents September 1, 2021) and ddmmyyyy (2 "dd" when the day is 10-31, ex: 14092021 represents September 14, 2021).

    How would I convert this into dd/mm/yyyy so google sheets will recognize it as the correct date ?

    Thanks so very much!

    • Hello Cisca,

      Changing Number format won't help here because of the way Google interprets dates (as numbers).

      You need to use the method suggested earlier in the comments: extract the necessary parts of your number as a day, month, or year accordingly. For example (supposing the number is in A2):
      =DATE(RIGHT(A2,4),MID(A2,3,2),LEFT(A2,2)) (for when the day is 10-31)
      =DATE(RIGHT(A2,4),MID(A2,2,2),LEFT(A2,1)) (for when the day is 1-9)

  3. Can I change the Date format to Capitalize the Month like this:

    12SEP21. or 12SEP2021??

  4. hello! in my calendar, the dates are appearing as mm/dd/yyyy and i would only like them to appear as a single double-digits number. is there anyway i can fix this? thank you

    • Hello,

      If I understand you correctly and you'd like to see the days only, just set your custom date format. If that's not exactly what you need, please provide a couple of examples of the dates you have and the dates you'd like to see.

  5. Hi, my sheet requires others to change dates. If one double clicks the cell shows a calendar from which to choose a new date. The problem is once changed, the new date reverts back to a default. How do I lock the format I want but still allow others to double click and change it from the pop up calendar. (Also how do I get the ticker you have on your site. Thanks. Alan

    • Hi Alan,

      Dates usually change the format if they're set to Automatic or just Date. Try applying the format using custom settings and the date should always display the same format.

      As for the ticker, what do you mean exactly?

  6. Thanks a ton for such comprehensive coverage!

  7. Hi, I am getting date in ISO 8601 format, how can convert it into days.
    Ex-date- 2021-02-22T17:18:40+00:00 , I am able to convert 2021-02-22 this part but how can I proceed with the remaining part.

    • Hi Aman,

      Supposing your ISO date is in A1, to get the time part as well, you can use this formula:
      =TIMEVALUE(MID(A1,12,8))
      Don't forget to apply the 'Time' format afterwards.

      Or even get both date and time with one formula:
      =DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8))
      Don't forget to apply the 'Date time' format afterwards.

  8. hi
    i was trying convert date value which is in text data type into date data type i did try using DATEVALUE and TO_DATE combination to turn it into number data type but it is not changing i also tried using query function with format but it doesn't work to so pls can you suggest something it would helpful

    • Hi Shubham,

      Please specify how your 'text date' looks like and how you'd like it to see as a result. You can also provide the formulas you used, I'll see if there's a mistake somewhere.

  9. Hi, how to convert a date like this 2021-07-27T15:30:47.000Z to MM/DD/YYYY

  10. Looks like a useful site/article, unfortunately the ridiculous ticker running across the bottom of the page is so distracting I gave up and left your site. Even the little minimise icon just makes it smaller, but no less annoying.

    • Thank you for your feedback. Sorry to hear it didn't let you finish the article. We'll make the bar static when minimized as soon as possible.

  11. How come you cant make the cells understand that they are dates. And sort them by date, month and year. Makes me think Im an iddeeet. Bye

  12. Hi,
    I'm still having trouble with my google sheet. Despite changing the locale and time zone to UK and formatted the date, it still appears in MM/DD/YYYY instead of DD/MM/YYYY. Can I email my google sheet to you and have it checked?

    Thanks.

    • Hi Leng,

      Sure, feel free to share your spreadsheet with us – support@apps4gs.com. Please also highlight cells that don't display correctly or reply here where to look at.
      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.

  13. Hi Natalia--thanks for all your comments above. I've got a quandary with Sheets automatically converting a string to a date when an array function is pulling values from cells with either two-digit or three-digit strings like "4 5" or "1 2 3". The displayed values are respectively "44291" and "37623", respectively. Other cells that the array function uses that are populated with four-digit or one-digit strings like "4 5 6 7" "1" are accurately reflected as displayed.

    It's easy to replicate the problem by simply typing in the string and Sheets will automatically change the number to a date. And then you can force it to use Text as a format in that instance and it will be accurately displayed. However, choosing Text as the format of the affected cells in the array doesn't change the display.

    Is there a way to somehow constrain the source cells to be pulled in as text rather than as a date? I couldn't find a function to do that and I can't just do a concatenate because of the complicated nature of how it's building the strings.

    Thanks for your assistance!

    Scott

    • Hi Scott,

      I'm unable to reproduce the problem with the concatenation – the strings are merged together as text. Could you perhaps share the exact formula you're using?

  14. Dear Natalia,

    How can I change the timestamp from 10/20/2072 19:06:56 (this came when I did the importdata of the query) to 05/27/2021 09:33:56 (the actual timestamp from my query). But on our data warehouse the dates are correct like this (05/27/2021 09:33:56)

    • Dear Shuja,

      Go to File > Spreadsheet settings and make sure the locale and time zone are the same as in the correct spreadsheet.

      If this doesn't help, consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) and specify where the formula is.
      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.

  15. Dear

    Please help in timestamp function
    i have applied Timestamp function in doer sheet its working but issue is that we he done work timestamp taking current Date & time for Example:

    A Task Which Plan to do 25-05-2021 09:15 AM
    Doer Actual timestamp Do it 25-05-2021 09:20 AM

    But When he open after an hours sheet Actual Time Showing Current date time

    Please suggest here what can i do here
    i appropriate your positive response

    • Dear Rahul,

      If you use NOW() to stamp the time, please bear in mind that NOW is volatile function. It recalculates itself in all cells on each start and once anything at all is edited in your spreadsheet. There are 3 ways to change this behaviour:

      1. Open the spreadsheet, go to File > Spreadsheet settings > Calculation and pick one of three existing settings for Recalculation.
      2. Enter static dates and time manually or using the following keyboard shortcuts:
        • Ctrl+; for date
        • Ctrl+Shift+: for time
      3. Or you may try to find a solution here – an overview of Google Apps Script with a lot of helpful content and links: https://developers.google.com/apps-script/overview

  16. i am able to use datevalue to convert 04/11/2019 to 43773 , but i get #VALUE! while trying to apple samer datevalue to 6/25/19

    i have tried cahnge the system dater as well
    I tried copying as values
    i tried formatting as text

    no success
    :(

    • Hello Terence,

      Please consider sharing your spreadsheet where the formula doesn't work with us (support@apps4gs.com). If you have confidential information there, you can replace it with some irrelevant data, just keep the dates and their formats.

      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 it.

  17. Hello, I am in a special configuration...

    I have :
    Nov 5, 2018, 1:33:05 AM

    I want : 05/11/2018 01:33

    Impossible with Excel, and I tried with Google Sheet but it seems too difficult for me... :(

    Thanks for your help.

    Best regards,
    JP

  18. This article was really helpful with easy instructions to change MMDDYY to DDMMYY. Thankyou

  19. I have been writing data in google sheet in DD-MM-YYYY but it has taken data in MM-DD-YYYY format, so when I sort the date, I am receiving wrong sequence

    • Hello Madhur,

      Dates in Google Sheets are always sorted from the newest to the latest or back. If you want to sort only by days or by months, you will have to either convert dates to text or extract days/month to a neighbouring column and use it to sort data.

  20. Hi and thanks for the great information.
    I'd like to convert the date to Solar calendar date but I couldn't find a way to do so.
    Could you please help me with that?
    warm regards

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 :)