Как сделать подсчет дней в гугл таблице

Добавил пользователь Евгений Кузнецов
Обновлено: 04.10.2024

Есть таблица расходов в Google Sheets, в ней два столбика:

сумма (столбец C числовой формат)
дата (столбец D "11.12.2018")

Необходимо суммировать расходы по месяцам.

Пытаюсь использовать функцию SUMIF : SUMIF(D:D;Условие;C:C) . Вся проблема с этим самым условием. Если поставить в условие конкретный день в кавычках, например "11.12.2018", то все работает, сумма за день считается. А это говорит о том что формула воспринимает дату как строку и сравнивает ее со строкой. И вроде как на место строки можно поставить шаблон. Но! если я вместо "11.12.2018" ставлю в формулу ". 12.2018" или "*.12.2018" чтобы найти сумму за декабрь подсчет дает 0.

У меня есть таблица Google, которая выглядит примерно так:

Прямо сейчас, если я вручную укажу количество минут между двумя временами дня. Есть ли простой способ рассчитать дельту времени и сделать это для меня в электронной таблице?

Как только вы это сделаете, вы можете вычесть поля, чтобы получить разницу, как отметил Стефано Палаццо в своем ответе.

Да, если ваши поля времени правильно отформатированы (нажмите Формат → Число → Время ), вы можете просто добавить и вычесть время:

Это даст вам время дельта как HH:MM:SS . И если вы хотите , чтобы вычислить количество минут, вы можете использовать Hour() , Minute() и Second() функцию на этом поле:

Конечно, если есть високосные секунды, изменения часового пояса или если событие занимает больше 24 часов, вам все равно придется корректировать результаты вручную.

Предупреждение

Если одно событие продлится после полуночи, скажем, с 23:50 до 00:10, это будет отрицательное время!

Лучший способ

Это больше не работает. Я думаю, что вам нужно выбрать Формат> Число> Часы (против времени). Я получаю очень странное десятичное число, когда пытаюсь вычесть простую почасовую разницу.

Если кто-то хочет получить дату и время в одной ячейке, он может объединиться, просто добавив: A1 + B1.

Если вы добавите следующую формулу в D2 , то минуты будут рассчитаны автоматически:

формула

Разъяснения

Разница между временами в десятичном формате выражается в днях. Следовательно, умножение на время 24 на 60 приведет к минутам.

замечание

Сегодня задача следующая. Возьмем диапазон ячеек с датами, и подсчитаем, сколько ячеек соответствуют определенному диапазону дат. Для примера я взял ситуацию, в которой у нас есть люди и даты их рождения. Мы хотим узнать, сколько дней рождения было в определенные года. Данную формулу можно адаптировать под другие нужды, например у Вас магазин, и есть даты отгрузки, нужно подсчитать, сколько отгрузок было в определенный диапазон дат. Как всегда, это не просто таблицы (Excel) - это Google таблицы, как инструмент, доступный каждому, у кого есть аккаунт в компании Google.

Для подсчета количества ячеек, содержащих дату соответствующую периоду, будем использовать функцию COUNTIFS (СЧЁТЕСЛИМН).

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

Как работает данная формула? Функция COUNTIFS (СЧЁТЕСЛИМН) подсчитывает ячейки, которые соответствуют нескольким критериям. В нашем случае мы предоставляем два критерия:

1. Начальная дата;

2. Последняя дата;

Даты в моем примере, представлены в диапазоне (B2: B10) и работают для обоих критериев.

Функция DATE упрощает подсчет на основе аргументов год, месяц и день. В моем примере месяц и день имеют строгое ограничение, а год берется из столбца “D”.

Важно понимать: операторы "> В результате мы получаем следующую формулу:

=COUNTIFS(B2:B10;">="&DATE(D2;1;1);B2:B10;" =СЧЁТЕСЛИМН(B2:B10;">="&ДАТА(D2;1;1);B2:B10;" Еще о Excel. Когда будете вводить формулу, убедитесь, что диапазон имеет формат даты. Это важно, иначе, формула работать не будет.

Функция DATE в Google Sheets возвращает дату или серийный номер даты, комбинируя отдельные элементы дня, месяца и года, введенные в качестве аргументов функции.

Например, если в ячейку листа введена следующая функция DATE – = DATE (2016,01,16) , возвращается серийный номер 42385 , который ссылается на дата 16 января 2016 г.

Ввод дат как дат

В сочетании с другими функциями Google Sheets DATE можно использовать для создания самых разнообразных формул дат. Одним из важных применений этой функции – как показано в строках с 5 по 10 на изображении выше – является обеспечение правильного ввода и интерпретации дат некоторыми другими функциями дат в Листах Google. Это особенно верно, если введенные данные отформатированы как текст.

Функция DATE в основном используется для отображения даты, которая объединяет элементы даты – например, год, месяц или день из разных мест на листе, и для обеспечения того, чтобы даты, используемые в расчетах, интерпретировались как даты (числовые данные) вместо текстовых данных. ,

Синтаксис и аргументы функции DATE

Синтаксис функции относится к макету функции и включает в себя имя функции, скобки и аргументы.

Синтаксис для функции DATE: = DATE (год, месяц, день)

Год – введите год в виде четырехзначного числа (гггг) или ссылку на ячейку в соответствии с его местоположением на листе.
Месяц – введите месяц в виде двузначного числа (мм) или ссылку на ячейку в соответствии с его местоположением на листе.
День – введите день в виде двузначного числа (dd) или ссылку на ячейку в соответствии с его местоположением на листе.

Ошибки и корректировки

Пример функции DATE

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

Перечисленные формулы предназначены для примера использования функции DATE.

строка 5 входит в первый день текущего месяца
строка 6 преобразует текстовую строку (ячейка A5) в дату
строка 7 отображает день недели для данной даты
строка 8 считает дни между текущими и предыдущими датами
строка 9 преобразует юлианский номер дня (ячейка A9) в текущую дату
строка 10 преобразует текущую дату (ячейка A10) в юлианский номер дня

Приведенная ниже информация охватывает шаги, используемые для входа в функцию DATE, расположенную в ячейке B4. Выходные данные функции в этом случае показывают составную дату, созданную путем объединения отдельных элементов даты, расположенных в ячейках с A2 по C2.

Ввод функции ДАТА

Варианты ввода функции и ее аргументов в таблицу включают в себя:

Введите вручную полную функцию – просто помните, что порядок должен быть гггг, мм, дд , например = ДАТА (2016,01,16) или = ДАТА (A2, B2, C2) , если используются ссылки на ячейки.
С помощью окна автоматического предложения введите функцию и ее аргументы.

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

Разделитель запятых

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

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

Нажмите ячейка D4 , чтобы сделать ее активной ячейкой: здесь будут отображаться результаты функции DATE.

Введите знак равенства = , за которым следует название функции – дата.

Когда вы вводите , появится окно авто-предложение с именами и синтаксисом функций, которые начинаются с буквы D.

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

Нажмите ячейку A2 на листе, чтобы ввести эту ссылку на ячейку в качестве аргумента год .

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

Нажмите на ячейку B2 , чтобы ввести ссылку на эту ячейку в качестве аргумента месяц .

После ссылки на ячейку введите еще одну запятую , .

Нажмите ячейка C2 , чтобы ввести эту ссылку на ячейку в качестве аргумента день .

Нажмите клавишу Enter на клавиатуре, чтобы ввести закрывающую круглую скобку ) и завершите функцию.

Дата должна появиться в ячейке B1 в формате 15.11.2015.

При нажатии на ячейку B1 полная функция = ДАТА (A2, B2, C2) появляется на панели формул над рабочим листом.

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

Изменение формата даты

Выделите ячейки на листе, которые содержат или будут содержать даты.

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

Изменение региональных настроек

Как и во многих онлайн-приложениях, в Google Sheets по умолчанию используется американский формат даты, также известный как среднесрочный индекс MM/DD/YYYY .

Если в вашем местоположении используется другой формат даты – например, в Google Листах с прямым порядком байтов (ГГГГ/ММ/ДД) или с прямым порядком байтов (ДД/ММ/ГГГГ) Google Sheets может быть настроен для отображения даты в правильном формате, регулируя региональные настройки.

Чтобы изменить региональные настройки:

Нажмите Настройки электронной таблицы , чтобы открыть диалоговое окно Настройки .

В разделе Языковой стандарт в диалоговом окне нажмите на поле – значение по умолчанию США – для просмотра списка доступных настроек страны.

Нажмите на свою страну выбора , чтобы сделать ее текущим выбором.

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

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

Отрицательные серийные номера и даты Excel

По умолчанию Microsoft Excel для Windows использует систему дат, которая начинается в 1900 году. Ввод серийного номера 0 возвращает дату: 0 января 1900 года. Кроме того, функция DATE в Excel не будет отображать даты до 1900 года.

Google Sheets использует дату 30 декабря 1899 года для серийного номера ноль, но в отличие от Excel, Google Sheets отображает даты до этого, используя отрицательные числа для серийного номера.

Например, дата 1 января 1800 года приводит к порядковому номеру -36522 в Google Sheets и позволяет использовать его в формулах, таких как вычитание 1 января 1850 года – 1 января 1800 года, что приводит к значению 18 262 – количество дней между двумя датами.

Юлианские номера дня

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

Например, на изображении выше юлианский номер дня в ячейке A9 – 2016007 – состоит из семи цифр, причем первые четыре цифры номера представляют год, а последние три дня года. Как показано в ячейке B9, это число представляет седьмой день 2016 года или 7 января 2016 года.

Аналогично, число 2010345 представляет 345-й день 2010 года или 11 декабря 2010 года.

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

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

СЕГОДНЯ и ТДАТА

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

Для этого можно использовать функции =СЕГОДНЯ() или =ТДАТА(). Аргументы в обеих случаях остаются пустыми. Первая выводит в ячейке сегодняшнюю дату, а вторая еще и время.

ВРЕМЯ и ДАТА

Числа можете не писать вручную, а сослаться на нужные ячейки. Например, =ВРЕМЯ(С1;С2;С3).

Если нужно, чтобы в ячейке отобразилось число, которое показывает год, месяц или день от выбранного дня, то делается это с помощью соответствующих формул. В качестве аргумента указывается ячейка с датой, у меня это А6. В примере в А8 показан год, указанный в А6.

Используется это если нужно в формулах писать текущее число или месяц. Чтобы не делать этого вручную, можно в ячейку выводить актуальное значение, например, =ДЕНЬ(ТДАТА()), а уже ее потом использовать в формуле.

Для времени подобные формулы называются ЧАС(), МИНУТЫ() и СЕКУНДЫ(). В скобках пишем ячейку, в которой отображается время. У меня это А5, поэтому формула выглядит: =ЧАС(А5).

Чтобы отобразить с помощью них текущие показания, в качестве аргумента пишите (ТДАТА()).

Так в А12:А14 выведены значения для времени введенного вручную в А5 (они не меняются), а в D12:D14 будут отображаться текущие данные (постоянно меняются).

РАБДЕНЬ

Указываете рабочие дни, а функция выведет, какая это будет дата. Например, с текущего дня А2 отсчитаем 100 рабочих дней, выходные не учитываются. Последнем аргументом напишите диапазон праздников – D1:D5. В результате, отработав 100 дней и с вычетом праздников у нас будет 30 ноября.

РАЗНДАТ

Также можно указать такие аргументы:

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

ЧИСТРАБДНИ

Отличается от предыдущей тем, что считает только рабочие дни между двумя датами, при этом вычитает праздники. Сначала напишите более раннюю дату – А2, затем ту, что идет позднее – А16, и укажите диапазон с праздниками – D1:D5.

ДЕНЬНЕД и НОМНЕДЕЛИ

ISOWEEKNUM() также выдает номер недели, но по стандарту ISO.

КОНМЕСЯЦА

ЧИСТРАБДНИ.МЕЖД

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

Вы можете ссылаться на ячейки с ними А2, А16. Во всех примерах выше я делала именно так.

А если нужно использовать сегодняшнюю дату, тогда аргументом будет ТДАТА().

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

Читайте также: