Evolcom.ru

Бытовая техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как посчитать сетевые дни между двумя датами, включая субботу в Excel?

Как рассчитать дни между датами в Google Sheets

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

Также смотрите нашу статью Как конвертировать электронные таблицы Google Sheets в XML

Одно из самых распространенных применений электронных таблиц — создание календарей и обработка информации, связанной с датами, например, расписаний или графиков отпусков. Многим пользователям, создающим электронные таблицы, работающие с датами, необходимо подсчитать количество дней между двумя датами, то есть им нужно узнать, сколько дней проходит (например, с 1 июля 2018 года по 31 января 2019 года). Вы можете посмотреть на календарь и посчитать количество дней вручную, и это будет работать хорошо, если даты расположены близко друг к другу, но для большого количества дат или дат, которые находятся далеко друг от друга, помощь компьютера была бы желательна. К счастью, Google Sheets предлагает несколько способов найти количество дней между двумя датами.

Функция МИНУС

В отличие от Excel, в Google Sheets есть функция вычитания, которая очень удобна для расчета простых разностей дат. МИНУС – это функция вычитания листов, и из-за того, что даты хранятся внутри (как целые числа, описывающие количество дней, прошедших с определенной даты в прошлом), она отлично работает для вычитания одной даты из другой, если даты оба в том же формате. Синтаксис для МИНУС: = МИНУС (значение 1, значение 2),

Чтобы использовать MINUS, откройте в браузере пустую электронную таблицу Google Sheets. Ячейки B3 и C3 являются хорошим местом для ввода (в качестве примера) «4/4/2017» и «5/15/2017». Теперь мы поместим функцию МИНУС в ячейку D3. Для этого щелкните внутри панели эффектов, затем введите «= МИНУС (C3, B3)» и нажмите Enter. После этого ячейка D3 вернет значение 40, как показано ниже.

гугл даты

Между 5/5/2017 и 5/15/2017 прошло 40 дней. Также вы можете найти разницу между датами, просто введя ссылки на ячейки вместо использования МИНУС. Например, щелкните ячейку E3 и введите «= C3-B3» в строке функций, как показано на скриншоте ниже. Аналогично, значение в ячейке E будет отображаться в формате даты, поскольку вы напрямую вычитали даты без МИНУСА. Выбрав Формат > Число и Число, формат ячейки можно преобразовать в целочисленное значение.

гугл даты2

Вы также можете сначала ввести ссылки на ячейки с более ранней датой. В строке функций введите ‘= B3-C3’, и в ячейке появится значение -40. Это подчеркивает, что 4/4/2017 отстает от 15 мая 2017 года на 40 дней.

Функция DATEDIF

DATEDIF – это функция, с помощью которой вы можете найти общее количество дней, месяцев или лет между двумя датами. Вы можете найти общее количество дней между двумя датами, введенными в электронную таблицу, или вместо этого включить даты в DATEDIF. Синтаксис для DATEDIF: DATEDIF (дата начала, дата окончания, единица измерения), Единицей функции может быть D (дни), M (месяцы) или Y (годы).

Читайте так же:
Как перечислить все числа между двумя или более числами в Excel?

Чтобы найти разницу между 04/04/2017 и 15/05/2017 с помощью DATEDIF, выберите ячейку, к которой вы хотите добавить функцию (F3 в нашем случае), и введите «= DATEDIF» на панели эффектов. Затем мы расширяем функцию скобками, содержащими ссылки на ячейки B3 и C3 с начальной и конечной датой. В конце функции также должны стоять единичные дни, или «D». Поэтому полная функция = DATEDIF(B3, C3, «D»), которая возвращает значение 40, как показано ниже.

гугл даты3

D ATEDIF также будет работать, если вы вставите дату непосредственно в формулу. Щелкните ячейку в электронной таблице, чтобы добавить DATEDIF, а затем введите ‘= DATEDIF (» 4/5/2017 «,» 15/15/2017″,» D») ‘на панели «fx». Выбранная ячейка будет содержать 40, как показано ниже.

гугл даты4

Функция DAY360

Google Sheets включает DAY360, который вычисляет разницу между датами для 360-дневного года. Календарь на 360 дней используется главным образом для финансовых таблиц, в которых могут потребоваться расчеты процентных ставок. Синтаксис для DAYS360: = DAYS360 (дата начала, дата окончания, (метод)), (Метод) – это необязательный индикатор, который можно включить в метод подсчета дней.

Чтобы использовать эту функцию в электронной таблице Google Sheets на даты 01.01.2016 и 01.01.2017, введите «01.01.2016» в ячейку B4 в качестве даты начала, а затем введите «01.01.2017» в C4 в качестве даты окончания функции. Теперь выберите ячейку D4, введите функцию ‘= DAYS360 (B4, C4)’ на панели эффектов и нажмите Enter. Тогда ячейка D4 будет включать в себя 360 дней между выбранными датами. Note что единственное реальное использование этой конкретной функции – это если вы работаете с процентными ставками.

гугл даты5

Функция NETWORKDAYS

Кроме того, N ETWORKDAYS вычисляет количество дней между датами, но она не совсем идентична другим. В этой функции мы включили только рабочие дни, а не выходные. Чистые рабочие дни следует читать как «Чистые дни». Таким образом, с помощью NETWORKDAYS можно найти общее количество дней между двумя датами, а также указать дополнительные выходные дни, чтобы исключить другие даты. Синтаксис для NETWORKDAYS: NETWORKDAYS (начальная дата, конечная дата, (выходные)),

В электронной таблице примера введите даты 4/4/2017 и 15/15/2017 в ячейки B3 и C3, чтобы продемонстрировать, как работает эта система. Выберите ячейку, в которую нужно включить общее количество дней, нажмите на панели «Эффекты», чтобы вставить функцию, а затем напишите формулу. Затем введите «= NETWORKDAYS (B3, C3)» и нажмите Enter, чтобы добавить функцию в выделенную ячейку. Общее количество дней между датами в ячейке NETWORKDAYS составит 29 NETWORKDAYS.

Введите «17.04.2017» в ячейку A3, чтобы добавить праздничную дату. Выделив ячейку NETWORKDAYS, щелкнув панель валют и добавив ссылку на ячейку A3, измените функцию. Это вернет 28 с дополнительным выходным днем, вычтенным из общего количества дней с помощью функции = NETWORKDAYS (B3, C3, A3).

Читайте так же:
Как подсчитать количество дней, недель, месяцев или лет между двумя датами?

гугл даты6

Другие важные функции, связанные с датой

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

    • Функция DATE преобразует указанный год, месяц и день в дату. Формат – ДАТА (год, месяц, день). Например, DATE (2019,12,25) возвращает «25.12.2009».
    • Функция DATEVALUE преобразует правильно отформатированную строку даты в целое число даты. Формат DATEVALUE (строка даты); строка даты может быть любой подходящей строкой, такой как «25/12/2019» или «23.01.2012 8: 5: 30».
    • Функция DAY возвращает день месяца, на который приходится конкретная дата, в числовом формате. Формат – ДЕНЬ (дата). Например, DAY («25.12.2009») возвращает 25.
    • Функция DAYS возвращает количество дней между двумя датами. Формат – ДНИ (дата окончания, дата начала). Например, DAYS («25/12/20189», «31.08.2009») возвращает 116.
    • Функция EDATE возвращает дату через определенное количество месяцев до или после указанной даты. Формат EDATE (дата начала, количество месяцев). Например, EDATE («31.08.2009», -1) возвращает «31.07.2009».
    • Функция MONTH возвращает месяц года, на который приходится конкретная дата, в числовом формате. Формат – МЕСЯЦ (дата). Например, MONTH («30.08.2009») возвращает 8.
    • Функция TODAY возвращает текущую дату в качестве значения даты. Формат – СЕГОДНЯ (). Например, на момент написания этой статьи TODAY () вернул бы «31.08.2009».
    • Функция WEEKDAY возвращает числовое значение, показывающее день недели предоставленной даты. Формат: WEEKDAY (дата, тип) и тип может быть 1, 2 или 3. Если тип равен 1, дни считаются с воскресенья, а воскресенье имеет значение 1. Если тип равен 2, дни считаются с понедельника и значение понедельника равно 1. Если тип равен 3, дни считаются с понедельника, а значение понедельника равно 0. Например, 30.04.2009 является вторником, а WEEKDAY («30.04.2009», 1) будет верните 3, в то время как WEEKDAY («30/04/2019», 2) вернет 2, а WEEKDAY («30/04/2019», 3) вернет 1.
    • Функция YEAR возвращает числовое значение, показывающее год предоставленной даты. Формат ГОД (дата). Например, YEAR («25.12.2009») вернет 2019.

    Как найти количество дней между датами в Google Sheets. Функции MINUS, DATEDIF, NETWORKDAYS и DAYS360 обязательно пригодятся, если в вашей электронной таблице есть несколько дат.

    Есть ли другие уникальные или интересные способы работы с датами в Google Sheets? Поделитесь ими с нами ниже!

    Как рассчитать дни между двумя датами в Google Таблицах

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

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

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

    Расчет общего количества дней между двумя датами

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

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

    Формула ниже дает общее количество дней между этими двумя датами.

    Приведенная выше формула показывает, что между датой начала и окончания проекта 274 дня.

    Легко, правда!

    При вычитании дат необходимо помнить об одной вещи. Как показано выше, количество дней не включает дату начала. Если вы хотите включить дату начала в результат, к полученным данным нужно добавить 1.

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

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

    Хотя получить общее количество дней между двумя датами легко и приятно, в большинстве случаев нужны только рабочие дни, а не общее число дней.

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

    В конце концов, если это проект, вы не можете ожидать, что ваша команда будет работать по выходным … верно!

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

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

    Приведенная ниже формула даст вам количество рабочих дней между этими двумя датами:

    Функция ЧИСТРАБДНИ (как следует из названия) дает вам сетевые дни между двумя датами (она принимает дату начала и дату окончания в качестве входных аргументов)

    Вы должны знать две вещи о функции CLEAN:

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

    Хотя это может сработать в большинстве случаев, есть два распространенных сценария, в которых вы можете захотеть изменить эту формулу:

    • Вам нужно исключить праздничные дни из рабочих дней
    • У тебя разные выходные. Например, вы можете выбрать только воскресенье, так как выходные, а суббота для вас — рабочий день. Или вы из стран, где выходные дни — пятница и суббота (а не суббота и воскресенье)

    Google Электронные таблицы — отличный инструмент для всего этого.

    Расчет рабочих дней без учета государственных праздников

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

    Ниже приведен примерный график отпусков, который я составил на 2020 год.

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

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

    Рассмотрим набор данных, подобный нижеприведенному:

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

    Эта формула принимает три аргумента:

    • Дата начала — B1 в этом примере
    • Дата окончания — B2 в этом примере
    • Диапазон с праздничными датами — E2: E12 в этом примере.

    Следует учитывать, что функция ЧЕТРАБДНЕЙ умеет автоматически планировать праздничные и выходные дни. Например, в период с 1 апреля по 30 декабря существует восемь праздничных дней.

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

    Расчет рабочих дней, когда выходные не суббота и воскресенье

    Хотя функция ЧИСТРАБДНИ автоматически предполагает, что выходные дни — это суббота и воскресенье, это может быть не так для всех. У некоторых из вас может быть только воскресенье, а некоторые могут работать в странах, где пятница и суббота нерабочие дни, а воскресенье — рабочее время.

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

    NETWORKDAYS.INTL — где INTL означает международный.

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

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

    Следующая формула даст вам результат:

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

    В этой формуле можно использовать четыре аргумента:

    • Дата начала — B1 в этом примере
    • Дата окончания — B2 в этом примере
    • Выходные — 7 в этом примере. Здесь вы указываете выходные дни, которые должна учитывать эта формула. Почему 7, посмотрите таблицу ниже.
    • Диапазон с праздничными датами — E2: E12 в этом примере.

    Почему я использовал 7, чтобы пятница и суббота стали выходными?

    Вот как закодирована формула. Цифры обозначают выходные дни и предварительно занесены в формулу.

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

    Вот все числа, которые можно использовать, и их значение в таблице:

    АргументВыходные дни
    1Суббота / воскресенье — выходные дни
    2Воскресенье / понедельник — выходные дни
    3Понедельник / вторник — выходные дни
    4Вторник / среда — выходные дни
    5Среда / четверг — выходные дни
    6Четверг / пятница — выходные дни
    7Пятница / суббота — выходные дни
    11Воскресенье — единственный выходной день
    12Понедельник — единственный выходной день
    13Вторник — единственный выходной день
    14Среда — единственный выходной день
    15Четверг — единственный выходной день
    16Пятница — единственный выходной день
    17Суббота — единственный выходной день

    Исходя из приведенной выше таблицы, если вы хотите, чтобы воскресенье было единственным выходным днем (при шестидневной рабочей неделе), вам нужно использовать 11 в качестве третьего аргумента в функции NETWORKDAYS. INTL.

    Расчет рабочих дней с непоследовательными выходными днями

    Предположим, что вы работаете только некоторые дни недели (понедельник, вторник и четверг).

    Расчет рабочих дней между двумя датами значительно усложняется таким образом.

    К счастью, это было учтено при разработке функции NETWORKDAYS.INTL.

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

    И вы делаете это, используя серию из семи последовательных чисел (где каждое число равно 1 или 0). Эти числа представляют семь дней в неделе. Первое число в ряду представляет понедельник, второе — вторник и так далее.

    «0» означает рабочий день, а «1» — нерабочий день. Таким образом, 0000011 будет означать, что первые пять дней (с понедельника по пятницу) рабочие, а суббота и воскресенье — выходные (или нерабочие дни).

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

    Итак, в нашем примере, где понедельник, вторник и четверг — рабочие дни, код будет 0010111.

    Вы можете использовать этот код в функции NETWORKDAYS.INTL как показано ниже:

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

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

    Рассчитать все понедельники между двумя датами

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

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

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

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

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

    Это не что иное, как разновидность примера, в котором мы вычисляем непоследовательные рабочие дни. В этом примере нам нужно указать только понедельник в качестве рабочего дня, а все остальные — выходные.

    Таким образом, строка цифр будет «0111111», где 0 обозначает рабочий понедельник.

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

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

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector