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

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

  • автор:

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

Пример текста, который надо переделать в дату и время — January 07, 2021 at 08:31PM, как этому придать такой вид: 20:31, 07.01.2020?

  • Вопрос задан более трёх лет назад
  • 945 просмотров

Комментировать
Решения вопроса 1

oshliaer

Alexander Ivanov @oshliaer Куратор тега Google Sheets
Google Products Expert

=DATE( REGEXEXTRACT(A1,"(\d)"), SWITCH(REGEXEXTRACT(A1,"\w"),"Jan",0,"Feb",1,"Mar",2,"Apr",3,"May",4,"Jun",5,"Jul",6,"Aug",7,"Sep",8,"Oct",9,"Nov",10,"Dec",11), REGEXEXTRACT(A1,"(\d),") ) + TIME( IF(REGEXMATCH(A1,"PM"),12,0)+REGEXEXTRACT(A1,"(\d):"), REGEXEXTRACT(A1,":(\d)"), 0 )

5ffbe39783c2f860252703.png

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

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

=ARRAYFORMULA( DATE( REGEXEXTRACT(A1:A12,"(\d)"), SWITCH(REGEXEXTRACT(A1:A12,"\w"),"Jan",0,"Feb",1,"Mar",2,"Apr",3,"May",4,"Jun",5,"Jul",6,"Aug",7,"Sep",8,"Oct",9,"Nov",10,"Dec",11), REGEXEXTRACT(A1:A12,"(\d),") ) + TIME( IF(REGEXMATCH(A1:A12,"PM"),12,0)+REGEXEXTRACT(A1:A12,"(\d):"), REGEXEXTRACT(A1:A12,":(\d)"), 0 ))

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

=ARRAYFORMULA( DATE( REGEXEXTRACT(A2:A100;"(\d)"); MATCH(REGEXEXTRACT(A2:A100;"\w");;0)-1; REGEXEXTRACT(A2:A100;"(\d),") ) + TIME( IF(REGEXMATCH(A2:A100;"PM");12;0)+REGEXEXTRACT(A2:A100;"(\d):"); REGEXEXTRACT(A2:A100;":(\d)"); 0 ) )

Текст вместо даты в Гугл Таблицах или смещение по годам

Текст вместо даты в Гугл Таблицах или смещение по годам

Наконец-то разобрал проблему, когда отображается формат текст вместо даты в Гугл Таблицах. Или та же ошибка, когда протягиваешь ячейку с датой, а вместо прибавления дня добавляется единичка к году в Google Spreadsheets. Ранее не доходили руки и вот проблема стала актуальной настолько, что пришлось гуглить.

На скрине ниже привёл пример, когда у нас есть столбец с форматом «Дата» и мы протягиваем ячейку вниз, получаем смещение не на один день → 06.06.2021, а на один год → 05.06.2022, чего собственно быть не должно.

ошибка при протягивании даты в Гугл Таблицах, когда добавляется год вместо числа

Решение проблемы с форматом даты Гугл Таблиц

Проблема до банальности проста. Дело в том, что Гугл просто не воспринимает введённую дату, как формат «Даты», потому что по умолчанию в «региональные настройки» стоит США.

Соответственно, чтобы Гугл Таблицы стали воспринимать ячейку и данные в ней как даты, нужно сначала выбрать Россию в «региональных настройках», т.к. в США формат даты начинается с года, а не дня.

Потом встать на столбец и присвоить ему формат «Даты».

Как вы видите, формат столбца поменялся и выравнивание стало по правой стороне — так Гугл Таблицы форматируют Даты.

Протянем дату вниз и проверим, что прибавляется один день, а не год.

Смещение даты в Гугл Таблицах на один день

Всё получилось и теперь при протягивании ячейки с датой в Гугл Таблице смещение будет идти по дням, а не годам.

Похожие записи:

  1. Поиск значения по столбцу и вывод даты первого совпадения
  2. Выделение строки цветом по значению в ячейке в гугл таблицах и excel
  3. Как вывести двойные кавычки в Гугл Таблицах
  4. Функция Query и сумма SUM по значению в Гугл таблицах

Гугл таблица. Убрать время из даты

Нужен макрос для преобразования даты и времени только в дату. При заполнении таблицы через форму (дата заполнения формы) ответ появляется следующего вида: 12-12-2022 16:23:45. Мне для корректной работы других формул нужна только дата, без времени. Возможно ли составить макрос, который будет удалять 10 знаков справа в выбранной ячейке? Спасибо.

  • google-spreadsheet
  • макрос
  • редактор-текста

Отслеживать
8,586 4 4 золотых знака 22 22 серебряных знака 36 36 бронзовых знаков
задан 17 дек 2022 в 11:36
11 2 2 бронзовых знака

И в чём проблема? В попытке усложнить задачу из-за непонимания предмета? 1) Открываем таблицу. 2) Идём в макросы: меню «Расширения/Макросы/Записать макрос». 3) Выделяем ячейку или колонку с «неправильными» датами и переходим в меню «Формат/Числа/Дата» и оно само всё отрабатывает как надо. Если нужно, то можно сделать и «Пользовательский» формат. 4) Сохраняем макрос и пользуемся в своё удовольствие. А то ишь чего надумали: удалять 10 знаков

17 дек 2022 в 13:30

Формат он меняет, но содержание ячейки нет. Время из-за смены формата отображения никуда не исчезает.

17 дек 2022 в 13:41

а почему вы тогда не указали эту информацию в посте: какой формат на выходе вам нужен? Формат «Дата», он и есть — дата. Нужен текстовый? Так добавьте в макрос ещё одно действие: «Формат/Числа/Обычный текст».

17 дек 2022 в 16:25

Мне не нужен формат отображения. Он не меняет содержимое. Нужно изменить 12-12-2022 16:13:28 на 12-12-2022. Дата заполнения формы добавляется автоматически таким видом. И в последующих формулах она не работает. Если руками убираю дату, всё ок. Но я не могу менять по 50 дат в день.

17 дек 2022 в 23:38

Похоже вы меня не понимаете. Если ваша формула неправильно считает даты, то проблема с формулой. Ну да ладно. Пойдём другим путём, как вы озвучили: заменой текста. 1) идём в макросы и стартуем запись. 2) выделяем ячейку или диапазон с датами 3) находим меню «Правка/Найти и заменить», а в появившемся окошке чекаем галочку «Поиск с использованием регулярных выражений» в поле «Найти» вставляем следующую последовательность — \s\d+:\d+:\d+ 4) нажимаем кнопку «Заменить все». 5) останавливаем запись макроса. ГОТОВО.

Функции Google Таблиц для работы с датой и временем

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

На скриншоте во втором столбце результат действия формулы, а в третьем – текст самой формулы.

Далее я расскажу про каждую функцию отдельно. Отмечу, что в качестве даты можно использовать ссылку на ячейку с таковой или указывать дату напрямую в формуле в формате «01.02.2015».

Функция TODAY (СЕГОДНЯ)

возвращает сегодняшнюю дату. Аргументов у нее нет – сегодняшний день и есть сегодняшний. Результат вычисления функции обновляется вместе с остальными ячейками при любом изменении в документе.

Функция NOW (ТДАТА)

возвращает текущие время и дату. Аргументов тоже нет. Если ее не отформатировать – в ячейке будет отображаться и то, и другое:

2

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

3

Сверху неотформатированная ячейка, снизу – ячейка с временным форматом.

Функция NETWORKDAYS (ЧИСТРАБДНИ)

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

Между первым октября 2015 года и 18 февраля 2016 года – 101 рабочий день.

Кроме того, даты можно задать и в самой формуле, хотя это и менее гибкий способ – ведь в ячейках их менять удобнее:

Можно задать в качестве второго аргумента функцию TODAY – и каждый день у вас будет отображаться актуальное количество рабочих дней, прошедших с определенной даты (аналогично можно задать TODAY и как первый аргумент – и отслеживать количество рабочих дней ДО определенной даты):

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

Удобнее всего их задать в диапазоне, а в функции сослаться на этот диапазон. Диапазон может выглядеть так:

Рабочих дней стало 95.

Функция NETWORKDAYS.INTL (ЧИСТРАБДНИ.INTL)

предыдущей возможностью задать нестандартную рабочую неделю. Это третий аргумент, и задается он в виде «0000011», где нули – это рабочие дни, а единицы – выходные. Допустим, для шведской четырехдневной недели формула будет выглядеть так:

10

И рабочих дней стало 77.

Функция WORKDAY (РАБДЕНЬ)

возвращает дату, которая наступит по прошествии определенного количества дней (второй аргумент) с определенной даты (первый аргумент):

В данном примере мы ссылаемся на ячейку B7, в которой стоит дата 01.10.2015. По прошествии 155 рабочих дней с этой даты наступит пятое мая.

Функции DAY (ДЕНЬ), MONTH (МЕСЯЦ), YEAR (ГОД)

возвращают соответствующий параметр из даты, которая является единственным аргументом этой функции:

В примере аргумент – сегодняшняя дата, заданная функцией TODAY.

Функция WEEKNUM (НОМНЕДЕЛИ)

возвращает номер недели. Первый аргумент – дата, а второй – необязательный – тип. По умолчанию тип равен 1, и это означает, что первый день недели – воскресенье, если задать аргумент тип равным 2, то первым днем недели будет считаться понедельник.

13

Функция DATEDIF (РАЗНДАТ)

вычисляет количество дней, месяцев и лет между двумя датами.

14

Первые два аргумента – стартовая и конечная даты. А третий – параметр, у него есть следующие варианты:

M – полные месяцы;

Y – полные годы. В примере находим разницу между 1 октября 2015 и 18 февраля 2016:

Видимо, что полного года еще не прошло – но можно вычислить десятичную долю года, воспользовавшись функцией YEARFRAC (ДОЛЯГОДА)

У DATEDIF есть еще три варианта последнего аргумента:

MD – количество дней после вычитания полных месяцев ( в примере 17 дней между 01.01.2013 и 18.02.2016);

YM – количество месяцев после вычитания полных лет ( в примере 1 месяц между 01.01.2013 и 18.02.2016);

YD – количество дней

после вычитания полных лет ( в примере 48 дней между 01.01.2013 и 18.02.2016).

15

Функция EOMONTH (КОНМЕСЯЦА)

возвращает последнюю дату месяца, следующего от заданной даты через определенное количество месяцев. Например:

Вернет 30.04.2016 в феврале (апрель = два месяца от февраля):

16

Если прибавить ко всей формуле единицу – то получим первый день следующего месяца, что тоже может пригодиться:

17

Функция WEEKDAY (НОМНЕДЕЛИ)

возвращает порядковый номер дня недели указанной даты (первый аргумент). Второй аргумент – тип, как у функции WEEKNUM. Для недели, начинающейся с понедельника, тип = 2:

18

Если же аргумент тип = 1, то четверг становится пятым днем недели, как видно на скриншоте:

четверг

Наконец, несколько функций для работы со временем. TIME (ВРЕМЯ)

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

Ну а SECOND (СЕКУНДЫ), MINUTE (МИНУТЫ) и HOUR (ЧАС)

возвращают одно соответствующее значение из даты. На скриншоте выше видно, что заснял я его в 22 часа, 22 минуты и 27 секунд – ведь во всех трех функциях в качестве аргумента задано текущее время – NOW().

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *