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

In this guide, you'll 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. Continue reading →

Comments page 4. Total comments: 209

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

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

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

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

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

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

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

    1. 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?

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

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

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

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

  8. 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
    :(

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

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

      1. Thanks :)

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

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

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

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

  13. Hi. I I've done concatenation of time stamp & text , with multiple entries in a single cell (based on some conditions).
    Everything is working well except sheet is not displaying it in chronological order.
    I guess sheet is reading whole thing as text. I tried To-Date formula before concat, but didn't work.
    Kindly help.
    I've also shared the sample to your support id.

    arrayformula(IF({H2:H} "", CHAR(10) & text({B2:B},"dd/mm/yy hh:mm ---- ") & {H2:H} & char(10))
    02/03/21 21:20 ---- Test1 Test1Test1 Test1 Test1 Test1 Test1 Test1 Test1 Test1

    27/02/21 06:54 ---- Test2 Test2 Test2 Test2 Test2 Test2 Test2 Test2 Test2 Test2 Test2 Test2

    27/02/21 07:01 ---- Test3 Test3 Test3 Test3 Test3 Test3 Test3 Test3 Test3 Test3 Test3 Test3

    1. Hi,

      When you concatenate date/time with text, such cells turn into text strings automatically. Google Sheets can't treat such cells as date/time. For that to happen, the date/time must be entered in any Date/time format Google recognizes as such.
      The only thing I can suggest here is to separate your date/time from the text and sort the table by the date/time column.

      1. thanks for your reply.

  14. I am trying to sort by date but it is sorting alpha. I have changed the date format numerous time and it looks ok but still sorts alpha.

    I have read everything I can find and have tried it all. Why won't it sort by date. I am using dd/mm/yyyy.

    1. Hi Sharon,

      It looks like your dates are treated as text. If you're unable to change the format, for me to be able to help you, please consider sharing your spreadsheet with us (support@apps4gs.com). If you have confidential information there, you can replace it with some irrelevant data or remove it. Just keep the dates and their format intact. I'll look into it.

      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, confirm by replying to this comment.

  15. thank you

  16. Hi,

    Great information.

    I was wondering is there a way to force the person entering the date to use the format, we wish to use. So for example I have set the date format to 24-Feb-2021. If someone types in for example 24.feb.2021 it overwrites the format and displays it that way as text. Oddly, if I type in 02/24/2021 it autocorrects it to the format I have chosen 24-Feb-2021. Anyway to force the user to enter it in this (24-Feb-2021) format only? Perhaps locking the format?

    Thank you.

    1. Hi Steve,

      Thank you for your feedback.

      When you apply the Date format to empty cells, all new dates entered into those cells are automatically displayed in that format.
      However, for that to happen, the date must be entered in any Date format Google recognizes as a date. Entering dates with periods won't work: periods turn your data into text automatically because periods are not used in Google Sheets dates. This is a very important concept to keep in mind.

      Thus, I can only advise you to inform other people on the correct way to enter dates (without periods).

  17. I have downloaded US credit card transactions which I would like to analyse with UK credit card transactions. I have attempted to change the format using the Create Custom Format in Numbers which failed (I made sure to include the / separator). Looking for a solution in Numbers or Google Sheets to convert the US date format to a UK date format. The file download is in .CSV format. Many Thanks

    1. Hello,

      Make sure the file you downloaded has a locale when you upload it to spreadsheets. This part of the blog post explains that.

      If setting the locale doesn't help, please create a copy of the file, remove all credit card numbers, emails, etc, but keep the dates intact, and share this copy with us: support@apps4gs.com. I'll look into it.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment.

  18. Hi - I'm trying to convert a numeric value (202101) to 2021-01-01. I tried the default date format but it's returning 1/30/2453, please help. Thank you.

    1. Hi Wing,

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

      You need to use the method Natalie used in the comment just above: extract the necessary parts of your number as a year, month, or day accordingly. For the number you provided, this formula will do (supposing the number is in A2):
      =DATE(LEFT(A2,4),RIGHT(A2,2),RIGHT(A2,2))

  19. Hello, I am converting a series of 6 text digits to a date

    ex. 010721 -> Jan 7th, 2021

    To do this I am using the function: =DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2)), which works fine.

    However, it automatically is forcing the year into 1921 rather than 2021.

    How can I set it to recognize the two-digit year code as belonging to the 21st century?

    1. Finally figured it out. For anyone who has a similar question, the solution can be as simple as adding 100 to the year in the formula.

      ex. =DATE(100+RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))

  20. I used query function to summarised date month and customer wise. there is date in col k and I used following query "=QUERY(Dashboard!A4:G,"select A, month (F)+1, sum(G) where A is not null group by month(F)+1, A label month(F)+1 'Month', SUM(G) 'GRN' format month(F)+1 'MMM'" ,1) but in result of month it is showing only jan while if I remove format function from my query then result is correct. please notify me where I am doing mistake

    1. Hello Shailendra,

      what if you try to format the entire date rather than a month? Please try this formula instead:
      =QUERY(Dashboard!A4:G,"select A, F, sum(G) where A is not null group by F, A label F 'Month', SUM(G) 'GRN' format F 'MMM'")

  21. Hello,

    We use a web application which export reports using ISO-8601, so the dates are like this:
    2020-11-12T00:00:00

    But when we import the csv, Google Spreadsheet are unable to understand it as a date, keeping as text.

    It's fairly simple to convert using text tools, but in scale it's time consuming for the users.

    It's seems like a long known bug. Do you know something about, or have a tip for us?

    Thank you in advance.

    1. Hello Cristiano,

      For Google Sheets to treat a value as a date, it must be in one of the date formats sheets understand. Having T in the middle doesn't make that happen.

      I'm afraid I can only suggest you replace T with a space. To avoid replacing all T's in the sheet, I'd look for parts of the text, e.g. find '0T0' and replace with '0 0'.
      If you find the standard replacement tool time-consuming or not really suitable for the job, feel free to check our Advanced Find & Replace add-on.

  22. 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?

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

    1. Hello!
      Unfortunately, you cannot change the default date format in Google Sheets. You can create and use a format convenient for you.

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

    1. Hello!
      Dates in Google Sheets are stored as numbers. Therefore, you need to convert them to text.

      =TEXT(E2,"m/dd/yyyy")&" "&"-"&" "&TEXT(F2,"m/dd/yyyy")

      Hope this is what you need.

      1. While you're at it, I would compress it to:

        =TEXT(E2,"m/dd/yyyy")&" - "&TEXT(F2,"m/dd/yyyy")

        In order to save space.

  25. 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")

    1. 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"))

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

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

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

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

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

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

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

  31. 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?

    1. Hello,

      Try entering '11/2018' into a cell and it will remain as '11/18'. Or you can use the DATE function and specify a day as well: only months and years will be displayed according to your custom date format.

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

    1. Hello Ankit,

      ArrayFormula simply enables the function to process the entire range of cells and return the result to each row at the same time. So if it doesn't work, the problem is in the formula you use within the ArrayFormula.

      I'd advise you to try the QUERY function described here instead to change the format of your dates.

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