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 of how 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 November 4, 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 > Spreadsheet 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 > More formats > More date and time formats:

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 not happy with the appearance of your dates, you can tailor your own custom date format:

  1. Select cells you want to format.
  2. Go to Format > Number > More formats > More date and time formats.
  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 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'")

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

    =QUERY(A1:C7,"select * format B 'dd mmmm, dddd'")

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 17, 2019 to a number. All the formulas below will return the same result: 43694.

=DATEVALUE("August 17, 2019")
=DATEVALUE("2019-8-17")
=DATEVALUE("8/17/2019")

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("8/17/2019","YYYY-MM-DD")

Here's how I converted my date – 8/17/2019 - to text and changed the format at the same time:

Google Sheets: convert dates to text and change the format.

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. ;)

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

  1. Ankit Kumar says:

    I have a problem in Google sheets
    If I use arrayformulawith if condition.how we change

    Like
    13/05/2020 to 20200513
    Continued... By use of array formula

  2. Synonym says:

    I have a major problem with dates in Google Sheets.

    I need to calculate duration of Column B MM/YY to Column C MM/YY with DATEDIF in Column D. I've got the formula correct, that's not the problem.

    The problem is that when I enter a date, such as 11/18, Google Sheets changes it to the current year, 11/20.

    What's more, the date is maintained properly in the first cell in each column, but mutates in the successive cells.

    I need to keep the dates as numeric or date formats so DATEDIF can calculate. When I change the dates to text formats, DATEDIF breaks.

    How can I force Google Sheets to maintain the dates as numerals AS ENTERED and not "autocorrect" them to the current year?

  3. Reader says:

    Wow, thank you a lot! What was pretty useful to me!

  4. Scott Salas says:

    I'm having an issue where my chart shows numbers instead of dates. Can't figure out how to change this.

    • Hello Scott,

      I'm afraid I need more details to help you out: your chart type, the format of your source data, and settings for your chart axis.
      Alternatively, you can share your spreadsheet with us (support@4-bits.com) with your source data and the chart, we'll look into it. Note. We keep that account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment.
      Thank you.

  5. Benyamin Limanto says:

    Thank you. It's really helpful, After changing the region to Indonesia, my Full Day name inside the Column changed from Monday to Senin. That is really a big help!

    This should be on the Google Docs Manual I think, because It's very useful, or maybe Google could endrose this kind of Community writting, it proven to be helpful for newbie like me :)

    • Thanks a lot for your feedback, Benyamin.

      I'm afraid since we're not affiliated with Google we can't affect what they describe in their Docs Editors Help. You can try leaving a feedback from one of their pages (there's a special link at the bottom of each page), but we don't know how they are processed.

      Anyway, I'm glad you've found the solution here. :)

  6. Muneer Akl says:

    Hi Natalia,
    Thank you for the valuable information.
    my question:
    Is there is a clue to change the Language of the displayed month and day. for example to write NOT Saturday but Samedi in French, or "السبت" in Arabic.

  7. Hermann says:

    Hi i have 1 problem with dates if i type 23/8 it must put in the date 23/8/2020 according to my farnatting but it doesnt it only show 23/8 as data and not a date because it reads the 23 as month if i type 8/23 then it show the date 23/8/2020. I need to type the month first and then the date for google sheets to read it as a date. If i type the date and then the month as with excel, google sheets does not read it as a date. This is very annoying. Please help

  8. Mina Kitsune says:

    Hello, I'm doing history work and I want sheets to have the year 1467 to 1477 and I was trying to get it to show how long between two dates it was. However I don't want to have to use negative numbers constantly. Sheets assumes everything happens after 1900 by default. How do I change it?

    =date(1470,1,1) right now prints out 01Jan3370

    Currently I'm doing, where A2 is a number of Miles or Km and A3 is a speed in the proper measurements something moves. IE how long does it take a person on foot to travel from Akita to Kyoto if they start on Feb 3rd 1467. Once I know the answer in days I want to make it readable. I realize I could fake it with 1967 but I'd rather not do that. Esp if I turn it into a timeline that goes into the present day.

    =rounddown(round(Distance!A$2/Distance!A3)/30) & " Months" & CHAR(10) &
    rounddown((round(Distance!A$2/Distance!A3)-rounddown(round(Distance!A$2/Distance!A3)/30)*30)/7) & " Weeks"& CHAR(10) &
    (round(Distance!A$2/Distance!A3)-(rounddown(round(Distance!A$2/Distance!A3)/30)*30)-(rounddown((round(Distance!A$2/Distance!A3)-rounddown(round(Distance!A$2/Distance!A3)/30)*30)/7)*7) & " Days")

    • Hello Mina,

      I'm afraid you can't change the way Google Sheets treats dates. By default, the first date there is 12/31/1899.
      For years between 0 and 1899, Google Sheets adds that value to 1900 to calculate the year. That's why you're getting 3370 when entering 1470 (=1470+1900).

      To calculate the number of days between such dates, you can try formulas like this:
      =DAYS(DATEVALUE("1477-1-1"), DATEVALUE("1467-2-3"))

  9. Alissa says:

    I'm trying to get sheets to display a specific date timeframe via using a formula:

    =E2&" "&"-"&" "&F2

    Instead of displaying dates from the indicated cells, it's showing numerical values ie:

    44100 - 44106

    When I want it to display:

    9/26/2020 - 10/2/2020

    How do I get sheets to display dates instead? I've already tried using variations of TO_DATE and DATE VALUE formulas with no luck.

  10. Carlos says:

    I'm aware how to change and customize date formats but do you know of a way to permanently change the default to date format of MMMM dd or October 12 vs the sheets default? I have tried changing locale but still not working.

    Thanks in advance.
    Carlos

  11. anonymous says:

    If i wanted to chain the date sequence by a month like: 11/1/2020 to 12/1/2020 to 1/1/2020

    how would i do that using the formula?

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