Сколько дней до дня рождения гугл таблицы
Перейти к содержимому

Сколько дней до дня рождения гугл таблицы

  • автор:

покупка

Как добавить количество лет, месяцев и дней до даты в таблицах Google?

В этой статье я расскажу о том, как добавить количество лет, месяцев или дней к дате в таблицах Google.

Добавьте количество лет на сегодняшний день в лист Google с формулой

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

Пожалуйста, введите эту формулу: =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2)) в пустую ячейку, в которую вы хотите поместить результат, а затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и вы получите результат ниже, как вам нужно. Смотрите скриншот:

док добавить годы месяцев дни 1

Внимание: В приведенной выше формуле A2 это ячейка даты, которую вы хотите добавить лет, и B2 — это количество лет, которое вы хотите добавить к дате.

Добавьте количество месяцев до даты в таблице Google с формулой

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

Пожалуйста, введите эту формулу: =EDATE(A2,B2) в пустую ячейку, где найдите результат, а затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить этой формулой, все даты были добавлены в указанное количество месяцев, как показано на следующем снимке экрана:

док добавить годы месяцев дни 2

Примечание: В приведенной выше формуле A2 — это ячейка даты, в которую вы хотите добавить месяцы, и B2 — количество месяцев, которое вы хотите добавить к дате.

Добавьте количество дней до даты в листе Google с формулой

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

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

док добавить годы месяцев дни 3

Внимание: В приведенной выше формуле A2 это ячейка даты, в которую вы хотите добавить дни, и B2 — количество дней, которое вы хотите добавить к дате.

Добавьте количество лет, месяцев и дней до даты в листе Google с формулой

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

Введите эту формулу: =DATE(YEAR(A2)+B2,MONTH(A2)+C2,DAY(A2)+D2) в пустую ячейку, чтобы получить результат, а затем перетащите дескриптор заполнения вниз, чтобы заполнить эту формулу, и количество лет, месяцев и дней было добавлено к дате следующим образом:

док добавить годы месяцев дни 4

Внимание: В приведенной выше формуле A2 это ячейка даты, которую вы хотите добавить годы, месяцы и дни, и B2, C2, D2 — это количество лет, месяцев и дней, которое вы хотите добавить к дате.

Лучшие инструменты для офисной работы

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

Вкладка Office: интерфейс с вкладками в Office и упрощение работы
  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Делаем таймер обратного отсчета в гугл таблицах

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

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

ТДАТА(NOW) — выводит текущее дату и время в формате «дата».
РАЗНДАТ (DATEDIF) — подсчитывает количество оставшихся дней, месяцев и лет между двумя датами. По умолчанию учитывается текущий день.
ДНИ (DAYS) — подсчитывает количество дней между двумя датами.
ЧАС (HOUR) — возвращает компонент часа в заданном времени, а также может вычислять разницу часов между двумя датами.
МИНУТЫ (MINUTE) — работает аналогично функции ЧАС, только для минут.

Допустим, мы хотим узнать сколько осталось дней до дня рождения хабра — 26 мая согласно Википедии.

Первоначальные настройки

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

В открытой таблице выбираем пункт меню «Файл» переходим в «Настройки таблицы» и на вкладке «Вычисления» выбираем «Обновления расчетов» — «При изменениях и каждую минуту».

image

Формулы для таймера

В примере я сделал таймер двух видов:

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

Единственное отличие — чтобы посчитать количество оставшихся целых месяцев и дней необходимо использовать функцию РАЗНДАТ, тогда как для вывода только количества дней достаточно функции ДНИ.

Также есть функция СЕКУНДЫ (SECOND) которая позволяет выводить секунды, но из-за того что временной диапазон вычисления ограничивается занчением «раз в минуту» — таймер секунд просто не будет успевать обновляться.

Пишем формулы для отсчета времени

В примере используются ячейки:

A2 — конечная дата, от которой ведем отсчет
B2 — текущая дата, вместо нее также можно использовать функцию ТДАТА (NOW).

Количество дней без учета целых месяцев:

Количество дней в целом

В самом простом варианте исполнения — выводим значения таймера в отдельные ячейки:

Используя функцию СЦЕПИТЬ (CONCATENATE) можно все данные вывести в одной ячейке.

Для этого примера формула имеет вид

или для подсчета только дней

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

Файл с примером находится по ссылке.

  • google sheets
  • countdown timer
  • развитие

Функции 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().

Дни рождения в Google Календаре с обозначением возраста

Уже много лет я пользуюсь Гугл календарем. Всегда было непонятно, почему в нем не отображается возраст людей из Google Контактов в момент наступления дня рождения, годовщины или других значимых событий.

Когда стал целенаправленно искать ответ на этот вопрос, обнаружил, что запрос на отображение возраста в календаре от гугл довольно популярный и однозначного ответа на него до сих пор нет. Это сподвигло меня создать решение на основе Google Apps Script.

Почему именно гугл скрипты?

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

Где код?

Не могу сказать что скрипт написан с нуля — я воспользовался наработками Bryan Patterson, которые были опубликованы аж 6 лет назад, в 2014 году.

Вот получившийся результат:

Birthdays and anniversaries with dates for your calendar.gs

// Глобальные переменные var contactsCal; var defaultCal; var now; var fromDate; var toDate; var events; // Инициализация (function() < contactsCal = CalendarApp.getCalendarById('addressbook#contacts@group.v.calendar.google.com'); defaultCal = CalendarApp.getDefaultCalendar(); // создаем события в календаре по умолчанию // var defaultCal = CalendarApp.getCalendarById('regrncqXXXXXXp07eihepag74@group.calendar.google.com'); //или другой календарь now = new Date(); fromDate = new Date(now.getTime()); toDate = new Date(now.getTime() + 31 * (1000 * 60 * 60 * 24)); // + 31 дней от текущей даты Logger.log('С даты: ' + Utilities.formatDate(fromDate, 'Asia/Yekaterinburg', 'MMMM dd, yyyy HH:mm:ss Z')); Logger.log('По дату: ' + Utilities.formatDate(toDate, 'Asia/Yekaterinburg', 'MMMM dd, yyyy HH:mm:ss Z')); events = contactsCal.getEvents(fromDate, toDate); Logger.log('Найдено событий: ' + events.length); >)(); function birthdayAgeToCalendar() < //дни рождения for (var i in events) < Logger.log('birthdayAgeToCalendar. дни рождения. Найдено: ' + events[i].getTitle()); var name = events[i].getTitle().split(" – день рождения")[0]; var contacts = ContactsApp.getContactsByName(name); Logger.log('birthdayAgeToCalendar. дни рождения. Name: ' + name); for (var c in contacts) < var bday = contacts[c].getDates(ContactsApp.Field.BIRTHDAY); var bdayMonthName, bdayYear, bdayDate; try < bdayMonthName = bday[0].getMonth(); bdayYear = bday[0].getYear(); bdayDate = new Date(bdayMonthName + ' ' + bday[0].getDay() + ', ' + bdayYear); Logger.log('birthdayAgeToCalendar. bdayDate: ' + bdayDate); >catch (error) <> var years = parseInt(new Date().getFullYear()) - bdayYear; try < defaultCal.createAllDayEvent(name + " – день рождения, " + years + " лет (года)", new Date(bdayMonthName + ' ' + bday[0].getDay() + ', ' + new Date().getFullYear())); Logger.log("Создано: " + name + " – день рождения, " + years + " лет (года)"); >catch (error) <> > > > function anniversaryAgeToCalendar() < //юбилеи for (var i in events) < Logger.log('anniversaryAgeToCalendar. Юбилеи. Найдено: ' + events[i].getTitle()); var name = events[i].getTitle().split("Юбилей у пользователя ")[1]; var contacts = ContactsApp.getContactsByName(name); Logger.log('anniversaryAgeToCalendar. юбилеи. Name: ' + name); for (var c in contacts) < var bday = contacts[c].getDates(ContactsApp.Field.ANNIVERSARY); //существующие типы данных https://developers.google.com/apps-script/reference/contacts/field var bdayMonthName, bdayYear, bdayDate; try < bdayMonthName = bday[0].getMonth(); bdayYear = bday[0].getYear(); bdayDate = new Date(bdayMonthName + ' ' + bday[0].getDay() + ', ' + bdayYear); Logger.log('anniversaryAgeToCalendar. bdayDate: ' + bdayDate); >catch (error) <> var years = parseInt(new Date().getFullYear()) - bdayYear; try < defaultCal.createAllDayEvent("Юбилей у пользователя " + name + ", " + years + " лет (года)", new Date(bdayMonthName + ' ' + bday[0].getDay() + ', ' + new Date().getFullYear())); Logger.log("Создано: " + "Юбилей у пользователя " + name + ", " + years + " лет (года)"); >catch (error) <> > > > function TriggersCreateTimeDriven() < //автоматически создаем новые триггеры для запуска // Deletes all triggers in the current project. var triggers = ScriptApp.getProjectTriggers(); for (var i = 0; i < triggers.length; i++) < ScriptApp.deleteTrigger(triggers[i]); >// а теперь создаем ScriptApp.newTrigger("birthdayAgeToCalendar") //дни рождения .timeBased() .onMonthDay(1) //день месяца .atHour(1) .create(); ScriptApp.newTrigger("anniversaryAgeToCalendar") //юбилеи .timeBased() .onMonthDay(1) .atHour(2) .create(); >

Как пользоваться?

1.Создайте новый Google Apps Script на гугл диске:

2.Скопируйте код скрипта.

3.Тестово запустите функцию “birthdayAgeToCalendar” — скрипт создаст события в календаре по умолчанию с указанием возраста для людей, у которых в течении 31 дня (но в текущем году) будет дни рождения:

6.Если всё в порядке запустите функцию “TriggersCreateTimeDriven” — она создаст автозапуск функций “birthdayAgeToCalendar” и “anniversaryAgeToCalendar” на каждое первое число на месяц вперед.

Итог

Считаю, что проблема отображения возраста в Гугл календаре при наступлении значимых событий решена. Также я хочу поблагодарить Евгения Намоконова (телеграм канал “Google Таблицы”) и Александра Иванова вместе с его телеграм каналом за помощь в напутствии и редактировании написанного кода.
Дополнительные подробности можно найти на GitHub.

Автор: Михаил Шардин,
25 декабря 2019 г.

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

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