Функции с датой и временем в google таблицах

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

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

СЕГОДНЯ функция

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

СЕЙЧАС функция

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

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

С 1 октября 2015 г по 18 февраля 2016 г. — 101 рабочий день.
Удобнее установить их в диапазоне и ссылаться на этот диапазон в функции. Диапазон может выглядеть так:
А формула такая:
ЧИСТРАБДНИ (функция ЧИСТРАБДНИ
Сейчас 95 рабочих дней.
Вверху — ячейка без форматирования, ниже — ячейка с временным форматом.
возвращает количество рабочих дней между двумя датами. Даты можно хранить в ячейках, как в этом примере:
Вы можете установить функцию СЕГОДНЯ в качестве второго аргумента, и каждый день будет показывать фактическое количество рабочих дней, прошедших с определенной даты (аналогично, вы можете установить СЕГОДНЯ в качестве первого аргумента и отслеживать количество рабочих дней ДО определенной даты. Дата):
Последний аргумент этой функции — праздники, то есть даты, которые не считаются рабочими днями.
Кроме того, даты могут быть установлены в самой формуле, хотя это менее гибкий способ; ведь их удобнее менять в ячейках:

ЧИСТРАБДНИ.ИНТЛ (функция

отличается от

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

И это было 77 рабочих дней.
РАБОЧИЙ ДЕНЬ
возвращает дату через определенное количество дней (второй аргумент) от заданной даты (первый аргумент):

В этом примере мы имеем в виду ячейку B7, которая содержит дату 10.01.2015. Через 155 рабочих дней с этой даты наступит 5 мая.

возвращает соответствующий параметр даты, который является единственным аргументом для этой функции:
НОМЕР НЕДЕЛИ (НОМЕРА НЕДЕЛИ)
В этом примере аргументом является сегодняшняя дата, как определено функцией СЕГОДНЯ.
Функции ДЕНЬ, МЕСЯЦ, ГОД

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

РАЗНДАТ (функция РАЗНДАТ
вычисляет количество дней, месяцев и лет между двумя датами.
Первые два аргумента — это даты начала и окончания. И третий — параметр, у вас есть следующие варианты:
D — полные дни;
М — полные месяцы;
Y — полные годы. В этом примере мы находим разницу между 1 октября 2015 г и 18 февраля 2016 г:
По-видимому, полный год еще не прошел, но вы можете вычислить десятичную дробь года с помощью функции ГОД
.
DATEDIF имеет еще три варианта последнего аргумента:

Как вводить даты и время в Excel

Если рассматривать русский язык, Excel позволяет вводить дату по-разному, и он понимает их все:

«Классическая форма	3.10.2006
Сокращенная форма	3.10.06
Использование скриптов	3-10-6
Использование дроби	03.10.06

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

16:45
И наконец, никто не запрещает указывать дату и время вместе одновременно через пробел, то есть
16:45:30
При желании вы можете дополнительно указать количество секунд, также вводя их через двоеточие:
Время вводится в ячейки через двоеточие. Например

Быстрый ввод дат и времени

Чтобы ввести сегодняшнюю дату в текущую ячейку, вы можете использовать сочетание клавиш Ctrl + F (или CTRL + SHIFT + 4, если у вас другой системный язык по умолчанию).

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

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

Если вы хотите, чтобы в ячейке всегда была текущая сегодняшняя дата, лучше использовать функцию СЕГОДНЯ):

Как Excel на самом деле хранит и обрабатывает даты и время

Если выбрать ячейку с датой и установить для нее Общий формат (щелкните правой кнопкой мыши Ячейки формата ячейки — вкладка Число — Общие), то вы увидите интересную картинку:

То есть с точки зрения Excel, 27.10.2012 15:42 = 41209.65417

Фактически, Excel хранит и обрабатывает любую дату точно так же, как число с целой и дробной частью. Целая часть числа (41209) — это количество дней с 1 января 1900 года (принятое за отправную точку) до текущей даты. И дробная часть (0,65417) соответственно дробная часть дня (1 день = 1,0)

Из всего этого следует два чисто практических вывода:

Во-первых, Excel не может работать (без дополнительных настроек) с датами до 1 января 1900 года. Но мы это переживем!
Во-вторых, в Excel можно выполнять любые математические операции с датой и временем. Именно потому, что это на самом деле числа! Но это уже открывает перед пользователем много возможностей.

Количество дней между двумя датами

Это считается простым вычитанием: мы вычитаем дату начала из даты окончания и преобразуем результат в общий (общий) числовой формат, чтобы показать разницу в днях:

Количество рабочих дней между двумя датами

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

Для такого расчета лучше использовать функцию ЧИСТРАБДНИ из категории Дата и время.

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

Примечание. Эта функция была включена в стандартный набор функций Excel с версии 2007. В более ранних версиях необходимо сначала включить надстройку пакета анализа.

Для этого перейдите в меню «Инструменты — Плагины» и установите флажок «Инструменты анализа).

После этого в Мастере функций в категории Date and Time появится нужная нам функция NETWORK DAYS).

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

Лучше прочитать здесь, как правильно рассчитать.

Сдвиг даты на заданное количество дней

Поскольку в системе дат Excel за единицу принимается день (см. Выше), для расчета даты, скажем, 20 дней от указанной, достаточно добавить это число к дате.

Сдвиг даты на заданное количество рабочих дней

Эту операцию выполняет функция РАБДЕНЬ).

Он позволяет рассчитать раннюю или позднюю дату от даты начала на необходимое количество рабочих дней (с учетом выходных и суббот, воскресений и праздников).

Использование этой функции полностью аналогично использованию функции ЧИСТРАБДНИ, описанной выше.

Вычисление дня недели

Разве ты не родился в понедельник? Нет? Конечно? Это легко проверить с помощью функции ДЕНЬНЕД в категории «Дата и время.

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

Вычисление временных интервалов

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

Здесь есть только один нюанс. Если при добавлении нескольких временных интервалов сумма превышает 24 часа, Excel сбросит ее до нуля и снова начнет добавлять с нуля. Чтобы этого не произошло, нужно применить к последней ячейке формат 37:30:55:

6 функций Google Sheet, которые помогают работать с данными

Google Sheet — это универсальный инструмент для систематизации и расчета данных. Этот облачный сервис — большое преимущество перед Excel. С ним легко сотрудничать, автоматизировать данные и не беспокоиться о их возможной потере — изменения сохраняются каждую секунду.

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

Присоединение текста в «Гугл Таблицах»

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

Амперсанд объединяет значения ячеек и отображает их в текстовом формате в другой ячейке. Кавычки «» заменяют текст, который вы хотите вставить.

Например, у вас есть такая таблица с ячейками:

Пример сочетания текста в Google Таблицах

В ячейке должен появиться общий: «4 поля и 5 ящиков». Ручной ввод, если ячеек много, занимает много времени; С помощью простой формулы это можно автоматизировать.

В нашем примере формула: = A1 & «» & B1 & «и» & A2 & «» & B2 объединяет значения. Выберите ячейку, в которой вы хотите отобразить сводную информацию, и введите эту формулу. Готовый. В новой камере теперь 4 ящика и 5 ящиков».

Совмещаем разные значения в ячейках таблицы и отображаем их в одном

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

Например, вам нужно объединить данные столбца в одну строку:

Пример объединения ячеек из столбца в одну

Записываем формулу в новую ячейку:

= ПРИСОЕДИНИТЬСЯ («-«; A1: A100)

Объединение цифровых ячеек в одну

Если вы поставите, например, «+» вместо знака «-», результат будет следующим:

Поместите нужный символ между кавычками

INDEX: возврат первого или последнего значения в листе «Гугл Таблицы»

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

Вам нужно рассчитать разницу между нижней ячейкой со старым значением. Это делается функцией ИНДЕКС (). Он позволяет вам возвращать значение ячейки, указав, какую строку и столбец искать в массиве данных.

Формула:

= ИНДЕКС (A: A; 1; 1) — всегда возвращает первую ячейку в столбце A.

Функция ИНДЕКС возвращает значение выбранной ячейки

И если вы объедините INDEX () и COUNTA (), он вернет последнее значение из таблицы

= ИНДЕКС (A: A; СЧЁТ (A: A); 1)

Импорт данных в «Гугл Таблицы»

Google Таблицы могут импортировать данные. Вы можете извлекать нужные числа из электронных таблиц из различных источников, включая XML, HTML, RSS и CSV, что полезно для импорта списков сообщений в блогах, настроек, инвентарных списков и т.д. Это можно сделать с помощью функции IMPORTXML:

Импорт содержимого списка или таблицы

Содержимое списка или таблицы можно импортировать из определенного URL-адреса, используя следующую формулу:

= IMPORTHTML («ссылка»; «таблица»; 4)

Импортировать содержание ссылки в Google Таблицы

Импорт CSV-файла

Импортируйте файл CSV с указанного URL:

= IMPORTDATA («ссылка на файл CSV»)

Импортировать данные из файла CSV

Импорт данных из RSS или atom-канала

Импорт данных из RSS-канала или атома тоже не составляет труда:

= IMPORTFEED («http://news.google.com/?output=atom»)

Импорт данных из файла RSS или Atom

Эти 6 функций помогут сделать работу с Google Таблицами проще и эффективнее. Используйте их, используйте их.

Работа с датой и временем в MySQL

В этой статье мы рассмотрим основы работы с датой и временем в MySQL.

Формат даты MySQL поддерживает различные форматы даты и времени. Их можно определить следующим образом:

ДАТА: сохраняет значение даты в формате ГГГГ-ММ-ДД. Например, 23.10.2008.
ДАТА ВРЕМЯ: сохраняет значение даты и времени в формате ГГГГ-ММ-ДД ЧЧ: ММ: СС. Например, 2008-10-23 10:37:22. Поддерживаемый диапазон даты и времени: от 1000-01-01 00:00:00 до 9999-12-31 23:59:59
TIMESTAMP похож на DATETIME с некоторыми отличиями в зависимости от версии MySQL и способа работы сервера.

Таблица, содержащая типы данных DATE и DATETIME, создается так же, как и другие столбцы. Например, мы можем создать новую таблицу под названием Заказы, которая содержит столбцы для номера заказа, заказанного товара, даты заказа и даты доставки заказа:

СОЗДАТЬ ТАБЛИЦУ `MySampleDB`orders` (
`order_no` INT NOT NULL AUTO_INCREMENT,
ТЕКСТ NOT NULL,
`order_date` DATETIME NOT NULL,
`order_delivery` DATE NOT NULL,
ПЕРВИЧНЫЙ КЛЮЧ (`order_no`)
)
ДВИГАТЕЛЬ = InnoDB;

Столбец ORDER_DATE — это поле MySQL DATE TIME, в котором мы пишем дату и время, когда был сделан заказ. Невозможно предсказать точное время даты доставки, поэтому мы записываем только дату.

Чаще всего для обозначения дат используются дефис (-) и двоеточие (:) для обозначения времени. Но мы можем использовать любой символ или вообще не добавлять никакого символа.

Например, допустимы все следующие форматы:

2008-10-23 10:37:22
20081023103722
2008/10/23 10.37.22
2008 * 10 * 23 * 10 * 37 * 22

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

Функция	Описание
ДОБАВИТЬ()	Добавить дату.
ДОБАВИТЬ ВРЕМЯ()	Добавьте время.
CONVERT_TZ()	Преобразование из одного часового пояса в другой.
CURDATE()	Возвращает текущую дату.
CURTIME()	Возвращает текущее системное время.
DATE_ADD()	Добавьте одну дату к другой.
MySQL DATE_FORMAT()	Устанавливает указанный формат даты.
ДАТА()	Извлекает часть даты из выражения даты или даты и времени.
РАЗНДАТ()	Вычтите одну дату из другой.
ИМЯ ДНЯ()	Возвращает день недели.
ДЕНЬ МЕСЯЦА()	Возвращает день месяца (1-31).
WEEKDAY()	Возвращает индекс дня недели аргумента.
DAYOFYEAR()	Возвращает день года (1-366).
ИЗВЛЕКАТЬ	Получить часть даты.
FROM_DAYS()	Преобразует номер дня в дату.
FROM_UNIXTIME()	Задает формат даты UNIX.
MySQL DATE_SUB()	Вычтите одну дату из другой.
ЧАС()	Узнай время.
ПОСЛЕДНИЙ ДЕНЬ	Возвращает последний день месяца для аргумента.
СОЗДАТЬ()	Создайте дату из года и дня в году.
ДЕЛАТЬ ВРЕМЯ ()	Возвращает значение времени.
МИКРОСЕКУНДА()	Возвращает миллисекунды аргумента.
МИНУТА()	Возвращает минуты аргумента.
МЕСЯЦ()	Возвращает месяц с прошедшей даты.
MONTHNAME()	Возвращает название месяца.
ТЕПЕРЬ()	Возвращает текущую дату и время.
PERIOD_ADD()	Добавьте интервал к месяцу-году.
PERIOD_DIFF()	Возвращает количество месяцев между двумя периодами.
КОМНАТА()	Возвращает четверть часа с даты, переданной в качестве аргумента.
SEC_TO_TIME()	Преобразуйте секунды в формат «ЧЧ: ММ: СС’.
ВТОРОЙ()	Возвращает второй (0-59).
MySQL STR_TO_DATE()	Преобразует строку в дату.
ДОПОЛНИТЕЛЬНОЕ ВРЕМЯ()	Остается время.
SYSDATE()	Возвращает время выполнения функции.
ФОРМАТ ВРЕМЕНИ()	Устанавливает формат времени.
TIME_TO_SEC()	Возвращает аргумент, преобразованный в секунды.
ПОГОДА()	Выберите в качестве аргумента долю времени переданного выражения.
TIMEDIFF()	Остается время.
TIMESTAMP()	С одним аргументом эта функция возвращает дату или выражение отметки времени. Сумма аргументов возвращается с двумя аргументами.
TIMESTAMPADD()	Добавьте интервал к дате и времени.
TIMESTAMPDIFF()	Вычитает интервал из даты и времени.
TO_DAYS()	Возвращает аргумент даты, преобразованный в дни.
UNIX_TIMESTAMP()	Получает дату и время UNIX в формате MySQL.
UTC_DATE()	Возвращает текущую дату в формате UTC).
UTC_TIME()	Возвращает текущее время в формате UTC).
UTC_TIMESTAMP()	Возвращает текущую дату и время в формате UTC).
НЕДЕЛЯ()	Возвращает номер недели.
ТРУДОВЫЙ ДЕНЬ()	Возвращает индекс дня недели.
WEEKOFYEAR()	Возвращает календарную неделю даты (1-53).
ГОД()	Возвращает год.
НЕДЕЛЯ ГОД()	Возвращает год и неделю.

Вы можете поэкспериментировать с этими функциями формата даты MySQL, даже не вводя никаких данных в таблицу. Например:

mysql> ВЫБРАТЬ СЕЙЧАС();

Давайте посмотрим, как значения даты MySQL заносятся в таблицу. Чтобы продемонстрировать это, мы продолжим использовать таблицу заказов, которую мы создали в начале статьи.

Начнем с добавления новой строки заказа. Поле order_no будет автоматически увеличиваться на 1, поэтому нам просто нужно вставить значения order_item, дату создания заказа и дату доставки. Дата заказа — это время, когда заказ вставлен, поэтому мы можем использовать функцию NOW (), чтобы заполнить строку текущими датой и временем.

Дата доставки — это период времени после даты заказа, который мы можем вернуть с помощью функции MySQL DATE_ADD (), которая принимает в качестве аргументов дату начала (в нашем случае NOW ()) и INTERVAL (в нашем случае 14 дней). Например:

ВСТАВИТЬ заказы (order_item, order_date, order_delivery)
ЗНАЧЕНИЯ (‘iPhone 8Gb’, СЕЙЧАС (), DATE_ADD (СЕЙЧАС (), ИНТЕРВАЛ 14 ДНЕЙ));

Этот запрос создает заказ на указанный товар с датой, временем доставки и двумя неделями позже в качестве даты доставки:

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

последнее обновление: 26.05.2018

MySQL имеет ряд встроенных функций для работы с датой и временем.

Получение даты и времени

Функции CURDATE и CURRENT_DATE возвращают текущую локальную дату как объект даты: SELECT CURRENT_DATE (); — 25.05.2018
ВЫБРАТЬ КУРДАТУ (); — 25.05.2018
UTC_TIME возвращает текущее местное время относительно GMT SELECT UTC_TIME (); — 17:47:45
UTC_DATE возвращает текущую местную дату относительно GMT SELECT UTC_DATE (); — 25.05.2018
Функции CURTIME и CURRENT_TIME возвращают текущее время как объект времени: SELECT CURRENT_TIME (); — 20:47:45
ВЫБЕРИТЕ ВРЕМЯ (); — 20:47:45
Функции NOW (), SYSDATE (), CURRENT_TIMESTAMP () возвращают текущую локальную дату и время по системным часам как объект даты и времени. Все три функции возвращают одинаковый результат SELECT NOW (); — 25.05.2018 21:34:55
ВЫБРАТЬ SYSDATE (); — 25.05.2018 21:34:55
ВЫБРАТЬ CURRENT_TIMESTAMP (); — 25.05.2018 21:32:55

Парсинг даты и времени

ГОД (дата) возвращает год от даты
ЧАС (час) возвращает час часа
MONTHNAME (date) возвращает название текущего месяца
СЕКУНДА (время) возвращает секунды времени
DAYOFYEAR (дата) возвращает номер дня в году
MINUTE (час) возвращает минуту времени
КВАРТАЛ (дата) возвращает номер квартала года
DAYOFMONTH (дата) возвращает день месяца в виде числового значения
DAYOFWEEK (date) возвращает день недели в виде числового значения
WEEK (date [, first]) возвращает номер недели в году. Необязательный параметр позволяет установить начальный день недели. Если этот параметр равен 1, то первым днем считается понедельник; иначе воскресенье
DAYNAME (date) возвращает название дня недели
LAST_DAY (date) возвращает последний день месяца как дату
МЕСЯЦ (дата) возвращает месяц даты

Примеры функций:

Функция EXTRACT

Функция EXTRACTION извлекает определенный компонент из даты и времени. Его формальный синтаксис:

ВЫПИСКА (единица измерения даты и времени)

Значение datetime представляет исходную дату и / или время, а значение единицы указывает, какой компонент даты или времени нужно получить. Единица может представлять одно из следующих значений:

МЕСЯЦ (месяц)
HOUR_SECOND (часы, минуты и секунды)
YEAR_MONTH (год и месяц)
DAY_SECOND (день, часы, минуты и секунды)
ЧАС (час)
DAY_MINUTE (день, часы и минуты)
Анус год)
СЕКУНДА (секунды)
МИНУТА (минуты)
MINUTE_SECOND (минуты и секунды)
DAY_HOUR (день и часы)
HOUR_MINUTE (часы и минуты)
Дневной день)

Примеры вызова функции:

Функции для манипуляции с датами

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

DATE_SUB (date; INTERVAL единица выражения) возвращает объект DATE или DATETIME, который является результатом вычитания определенного временного интервала из даты
TIME_TO_SEC (час) возвращает количество секунд с полуночи
TO_DAYS (date) возвращает количество дней с года 0
DATEDIFF (date1, date2) возвращает разницу в днях между date1 и date2
DATE_ADD (date; INTERVAL единица выражения) возвращает объект DATE или DATETIME, который является результатом добавления даты в указанный интервал времени. Интервал указывается с помощью единицы выражения ИНТЕРВАЛ, где ИНТЕРВАЛ предоставляет ключевое слово, выражение — это количество единиц, добавленных к дате, а единица — это тип единиц (часы, дни и т.д.). Параметр единицы может иметь те же значения, что и в функции ЭКСТРАКТ, то есть ДЕНЬ, ВРЕМЯ и т д

Примеры применения:

Форматирование дат и времени

DATE_FORMAT (date, format) возвращает объект DATE или DATETIME, отформатированный с использованием шаблона формата
TIME_FORMAT (date, format) возвращает объект TIME или DATETIME, отформатированный с помощью шаблона формата

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

% D: номер дня месяца с суффиксом (1-й, 2-й, 3-й…)
% i: минуты в формате 00..59
% r: время в 12-часовом формате (чч: мм: сс AM или PM)
% S: секунды в формате 00..59
% d: день месяца в числовом формате 00..31
% p: AM или PM
% Y: год в виде четырех чисел
% m: месяц в числовом формате 01..12
% b: сокращение месяца (январь… декабрь)
% W: название дня недели (воскресенье . суббота)
% h: время в формате 01..12
% H: время в формате 00..23
% M: название месяца (январь… декабрь)
% T: время в 24-часовом формате (чч: мм: сс)
% k: время в формате 0..23
% s: месяц в числовом формате 1..12
% y: год в виде двух чисел
% l: время в формате 1..12
% e: день месяца в числовом формате 0..31
% a: сокращение дня недели (вс . сб)