Как преобразовать дату в текст в гугл таблицах

Google таблицы: трансформация текста в дату и время, как?

К сожалению, всё это сильно зависит от формата строки.

Формула для массива

Возможно, SWITCH — не лучшая идея. Можно заменить на MATCH

• Facebook
• Вконтакте
• Twitter

Здоровский пример. Но что-то не могу никак победить задачку, что только не пробовал(((

Как преобразовать текст в дату в Google Таблицах

Работа с датами в Google Таблицах иногда может быть довольно неприятной. Это потому, что существует множество способов представления дат.

Проблема усугубляется, когда вы хотите выполнить операции, связанные с этими датами, например сортировку по дате или выполнение вычислений, связанных с датами.

В этом уроке я покажу вам, как преобразовать текст в даты в Google Таблицах .

Зачем преобразовывать текст в даты?

Мы часто находим вычисления, связанные с датами, которые в конечном итоге дают неожиданные результаты. Обычно это происходит, когда значение выглядит как дата, но фактически интерпретируется Google Таблицами как текстовое значение.

Вы также можете столкнуться с этой проблемой при импорте данных из внешних источников или преобразовании файла PDF в Google Таблицы.

В таких ситуациях правильнее было бы преобразовать текстовые значения так, чтобы Google Таблицы интерпретировали их как даты.

В этом руководстве мы покажем вам, как узнать, отображается ли значение как TEXT (ТЕКСТ) или DATE(ДАТА), и как преобразовать текстовые значения на дату в Google Таблицах.

Как узнать, действительно ли дата является текстовым значением?

Простой способ понять, является ли значение в ячейке датой или нет, — это использовать функцию ISDATE.

Если значение является датой, принятой в Google Таблицах, функция возвращает ИСТИНА, в противном случае она возвращает ЛОЖЬ.

Допустим, у нас есть даты, как показано на следующем снимке экрана:

Чтобы понять, содержит ли ячейка A2 фактическую дату или просто замаскированные текстовые значения, введите следующую формулу в пустую ячейку:

Поскольку дата в ячейке A2 нашего примера не принимается в Google Таблицах в качестве значения даты (для нашего местоположения), функция возвращает FALSE.

Однако та же формула, примененная к ячейке A3, вернет ИСТИНА, поскольку это действительная дата.

Есть много других способов узнать, действительно ли дата является ТЕКСТОВЫМ значением:

• Вы заметите, что содержимое ячейки выровнено по левому краю (если ширина ячейки больше, чем ширина ее содержимого). Значение даты всегда выравнивается по правому краю.
• В некоторых случаях, когда вы щелкаете по ячейке, вы увидите начальный апостроф даты в строке формул, сигнализирующий о том, что это текстовое значение.

Как преобразовать текст в дату в Google Таблицах

Как только вы определили, что дата действительно интерпретируется Google Таблицами как текстовое значение, вы можете преобразовать ее в дату.

Метод преобразования текущего текста зависит от вашей ситуации. Вот некоторые ситуации, когда дату можно интерпретировать как текст в Google Таблицах:

• Предыдущий пользователь мог работать с листом и сохранять значения даты в виде текста, используя апостроф (‘) перед значением даты или преобразовывая дату в формат обычного текста.
• Ваши даты могут быть введены в формате, отличном от формата даты, принятого в вашей версии Google Таблиц. Форматы даты, понятные в Google Таблицах, зависят от региона и языковых настроек. Например, у вас может быть дата в формате дд-мм-гггг, тогда как Google Таблицы принимает даты только в формате мм-дд-гггг в вашем регионе или языковых настройках.
• Возможно, вы импортировали PDF-файл в Google Таблицы и обнаружили, что даты указаны в странной текстовой форме, что-то вроде того, что показано ниже:

Давайте посмотрим на все три из вышеперечисленных сценариев на трех отдельных примерах.

Использование функций DATEVALUE и TO_DATE для преобразования текста в дату в Google Таблицах

Мы можем использовать комбинацию функций DATEVALUE и TO_DATE для преобразования даты, которая изначально находится в формате TEXT, в дату, которая находится в формате DATE Google Таблиц.

Функция DATEVALUE принимает значение даты в любом допустимом формате и возвращает ту же дату, что и серийный номер.

Примечание . По умолчанию все даты в Google Таблицах хранятся в виде целых чисел или серийных номеров, что упрощает их использование в расчетах. Серийные номера начинаются с 1 января 1900 года и увеличиваются на 1 каждый день после этого. Итак, 1 января 1900 года — это порядковый номер «1», следующий день — это порядковый номер «2» и так далее.

Синтаксис функции DATEVALUE следующий:

Здесь date_string — это дата в формате ТЕКСТ. Строки, принимаемые этой функцией, включают любую дату, которая обычно автоматически преобразуется при вводе без кавычек в ячейку. Дата также должна быть в формате, понятном Google Таблицам в вашем регионе и в языковых настройках.

Функция TO_DATE преобразует заданный серийный номер в соответствующую дату (в формате Google Таблицы DATE). Синтаксис функции TO_DATE следующий:

Здесь значение — это число, которое нужно преобразовать в дату. Это может быть числовое значение или ссылка на ячейку, содержащую числовое значение.

Возьмем пример. Допустим, у вас есть следующая дата:

Обратите внимание на начальный апостроф, означающий, что это ТЕКСТОВОЕ значение. Чтобы преобразовать эту дату в формате TEXT в правильное значение DATE, вам необходимо использовать функцию TO_DATE вместе с функцией DATEVALUE, как показано ниже:

Функция DATEVALUE преобразует текстовую дату в числовое значение, а функция TO_DATE примет это число и преобразует его обратно в фактическую дату, как показано на изображении ниже:

Изменение местоположения или языковых настроек электронной таблицы для преобразования текста в дату

Если вы считаете, что проблема может заключаться в региональных или языковых настройках ваших таблиц Google, есть два варианта.

Вы можете изменить дату на формат, приемлемый для ваших региональных настроек, или изменить настройки местоположения электронной таблицы.

Вы можете изменить принятый формат даты следующим образом:

• Перейдите в Файл-> Настройки электронной таблицы.
• Выберите вкладку «Общие».
• В разделе «Язык и часовой пояс» выберите требуемый параметр.
• Щелкните Сохранить настройки.

Устанавливая языковой стандарт и часовой пояс для вашей электронной таблицы, вы меняете валюту, дату и числовые форматы по умолчанию.

Преобразование текста в дату, когда дата указана в 8-значном числовом формате

Если ваша таблица Google импортирована из PDF-файла, нередко можно найти даты, отформатированные в странной текстовой форме, например, в виде 8-значного числа, как показано ниже:

В таких случаях два вышеуказанных метода не работают. Вам нужно будет извлечь день, месяц и год из заданной текстовой даты и передать их в функцию DATE (ДАТА), чтобы получить значение ДАТА в допустимом формате: ДАТА (год, месяц, день)

Вот шаги, чтобы преобразовать данную 8-значную текстовую дату в фактическое значение DATE:

• Извлеките год. Он состоит из последних 4 цифр текстовой даты (справа от значения). Для этого мы используем функцию RIGHT следующим образом: RIGHT (A2,4)
• Извлеките месяц. Он состоит из 3-й и 4-й цифр текстовой даты (которые находятся в середине значения). Для этого мы используем функцию MID следующим образом: MID (A2,3,2). Здесь мы извлекаем 2 цифры, начиная с 3-й цифры.
• Извлеките день. Он состоит из первых двух цифр текстовой даты (слева от значения). Для этого мы используем функцию LEFT следующим образом: LEFT (A2,2)
• Наконец, объедините их вместе в функцию DATE, чтобы создать значение ДАТА с извлеченными значениями дня, месяца и года.

Итак, окончательная формула будет такой:

Это должно дать дату в правильном формате, как показано ниже:

Важно отметить, что не все текстовые даты импортируются именно в этом формате. Порядок дня, месяца и года может варьироваться от случая к случаю.

Уловка состоит в том, чтобы эффективно использовать функции RIGHT, LEFT и MID для извлечения правильных чисел, представляющих день, месяц и год.

Ниже приведены еще несколько возможных форматов текстовых дат, а также соответствующая формула, которая может использоваться в каждом случае для преобразования их в фактические даты:

Обратите внимание, что в последней формуле на приведенном выше снимке экрана значение года в исходной текстовой дате содержит только 2 цифры (вместо 4).

Итак, чтобы получить 2020 год, мы объединили 20 со значением года, используя формулу: 20 & RIGHT (A4,2). Если мы этого не сделаем, таблицы Google вернут значение 1920 года вместо 2020 года.

Таким образом, вы можете преобразовывать даты в любом формате, извлекая значения дня, месяца и года и передавая их через функцию даты.

В этом руководстве мы показали вам три случая, когда вы можете найти даты, представленные в виде ТЕКСТА в таблицах Google.

Мы также показали вам, как преобразовать эти значения TEXT в фактические значения DATE . Мы надеемся, что вы нашли этот урок полезным.

Как преобразовать дату в строку в Google Sheet

Я пытаюсь понять, как преобразовать дату в строку в таблице Google.

У меня есть одно поле даты, которое имеет разные форматы. Я хочу создать еще один столбец, который буквально такой же, но в виде текста. Например, если бы у меня были следующие данные

Где ‘просто означает, что это строка, обратите внимание на дату.

6 ответов

Используйте функцию TEXTJOIN . Задайте пустой разделитель, TRUE для игнорирования пусто, для указанной ячейки используйте text1 и ничего не указывайте для text2.

Вы можете использовать функцию ТЕКСТ.

1. скопировать столбец в другой столбец
2. выберите скопированный столбец
3. перейдите в меню «Формат» -> «Число» -> «Обычный текст»

Я не понимаю, зачем тебе это нужно. Только что понял, что нет возможности определить формат дат в гугл листах. Но вы можете использовать свойство getDisplayValues . Вставьте этот код в редактор сценария:

А затем используйте его как обычную формулу:

A1Notation — строка типа «A1» или «B3: AD15».

Лист — имя листа, например «Лист1». Это необязательно, используйте ActiveSheet, если не указано

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

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 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 > Spreadsheet settings in the Google Sheets menu.
2. Find Locale under the General tab and pick the desired location from the drop-down list:

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:

The integers successfully turn into the format that you’ll recognize at a glance. These are default Google Sheets date formats:

Tip. You can find the same formats if you click on the 123 icon on the spreadsheet toolbar:

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:

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:

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:

Repeat till all necessary units are added (no worries, you’ll be able to add or remove them later):

Here’s what I can choose for Day:

Here’s what format I’ve created and how my dates look now:

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:

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:

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:

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:

DATEVALUE function for Google Sheets

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

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:

Google Sheets: convert date to text

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

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

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

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

You may also be interested in

Table of contents

138 comments to «How to change Google Sheets date format and convert dates to numbers and text»

I have a column with the day as 04, 07, 15 etc and they’re not consecutive (I’m putting together a silly holiday calendar). The month is a header at the top so I don’t include it in the day column data.

I’d like to automatically change the numbers in that day column to 01/04/2023 etc.
Is there any way to make that happen?

The numbers of the calender is at 2 4 6 8 10 how can i fix that

Sorry but your question is not clear. Could you please describe the problem in detail?

Wow this is amazing!

Appreciate your feedback, Mr_Leon! 🙂

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

I believe you will find a solution in this article: Extract date and time from a full «date-time» record.

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

you can change these using the Custom date formats.

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.

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.

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.

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.

You can adjust these via the Custom date and time format.

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

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.

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

This depends on your spreadsheet locale. You’ll find a solution in this part of the blog post above.

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?

Please see how to split date and time in Google Sheets in this blog post.

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?

What you see in the formula (data entry) bar, can and usually will differ from what you see in a cell. The displayed result in a cell depends on your spreadsheet locale and the date format applied to cells.

If changing locale and the date format doesn’t help, please share an editable version of your spreadsheet with us: support@apps4gs.com
I’ll look into the problem.

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.

You’re most welcome, John! 🙂

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!

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

Sorry, my bad! I tested the formula in regard to B5 and haven’t changed it to A2 🙂
Happy to hear it helps!

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.

Can you please provide an example of what you see in a cell exactly and what you’d like to get instead?

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

Please check this tutorial on how to delete the first/last N chars from cells.

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

You need to fill at least 2 cells with dates so Google Sheets could understand the desired pattern. Please see Method 2 in this blog post for details.

To get rid of the last comma, I’d advise you to open the custom format settings and make sure no comma is at the end there.

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.

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.

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.

What keys do you use exactly?

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!

I can see a comma right after column B in your formula. Try to remove it and see if the formula works then.

Thank you, that was my error, now sorted.

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!

I can see a comma right after column B in your formula. Try to remove it and see if the formula works then.

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.

to make Google Sheets understand your 20/1 as a date, enter 20/1/2022 first and then apply a custom date format to this cell (Day/Month). Custom date format will also help you set the date as Day/Month-Year.

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.

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.

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

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

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?

You can actually apply the Date format: Month (Aug)-Year(1930), to these cells with the standard option from the Google Sheets menu right away without removing the timestamp. But if removing the timestamps is necessary, you can quickly get rid of them (without losing the data type) using this tool.

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.

Check the regional settings of your result spreadsheet. I mentioned it here, please take a look.

A little correction:

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

the correct date is 4/11/1900

Indeed! Thanks for the comment, I’ve corrected the date!

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.

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

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!

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)

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

12SEP21. or 12SEP2021??

Only if you turn dates into text and wrap them in the UPPER function.

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

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.

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

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?

Thanks a ton for such comprehensive coverage!

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.

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.

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

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.

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

Try this formula:
=SUM(SPLIT(F24,»TZ»))

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.

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

Dates are actually tricky in spreadsheets and we all have different levels of expertise there 🙂

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?

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.

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!

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?

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)

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.

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

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

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

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.

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

Commas in such strings won’t allow Google Sheets to treat them as dates (even as custom ones, please read here). You need to remove commas first (we have a special tool for that if you’d like) and then apply any date-time format as described here.

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

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

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.

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

Since the Solar calendar is not incorporated into Google Sheets, I’m afraid there’s no standard way of solving your task. 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

I wish I could assist you better.

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( «», CHAR(10) & text(,»dd/mm/yy hh:mm —- «) & & 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

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.

thanks for your reply.

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.

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.

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

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

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.

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.

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

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?

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.

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

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'»)

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.

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.

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?

There’s an easy way to do that without the formula. It is described in this blog post (How to insert date and time in Google Sheets), please have a look.

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

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

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

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

When I want it to display:

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.

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

Hope this is what you need.

While you’re at it, I would compress it to:

In order to save space.

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

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

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

The date format depends on your spreadsheet locale. I described it in the second paragraph, please have a look.

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.

Thank you for your positive feedback.
Try changing the display language by following these instructions.

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

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

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.

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

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?

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.

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

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.