Auditportal.ru

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

Как исключить выходные на оси дат в Excel?

Как сделать разницу дат в excel?

Одна из самых интересных функций Microsoft Excel — это функция, о которой мало кто знает. Эта функция настолько малоизвестна, что Excel даже не описывает ее в справке, хотя, как ни странно, она там довольно хорошо описана. С помощью функции DATEDIF(), или DATEDIF(), можно вычислить разницу в днях, месяцах или годах между двумя датами.

Разве это звучит плохо? Иногда полезно иметь возможность точно и быстро записать, сколько времени прошло после какого-либо события. На ум приходит количество месяцев со дня рождения, сколько дней вы сидели на диете или как долго вы сидели на своей работе. Как много применений вы сможете найти для этой полезной функции? Более того, эти вычисления можно автоматизировать, чтобы каждый раз, когда вы открываете рабочую книгу MS Excel, там были именно те данные, которые вам нужны! Звучит интересно, не правда ли?

Функция DIVIDE() принимает три аргумента:

  • Дата начала — дата, с которой ведется счет
  • Дата окончания — до которой ведется счет
  • Единицы измерения — дни, месяцы, годы.

Она написана следующим образом:

=РАЗНДАТ(начальная дата;конечная дата;единица измерения)

Единицы измерения пишутся следующим образом:

  • «y» — разность данных за все годы
  • «m» — разница данных в целых месяцах
  • «d» — разница данных в целых днях
  • «yd» — разница данных в днях с начала года без учета года
  • «md» — разница в данных в днях без учета месяцев и лет
  • «ym» — разность данных в целых месяцах без учета года

Таким образом, согласно этой формуле, возраст человека на данный момент составляет:

=РАЗНДАТ(14.07.1984;22.03.2016;»y»)

как сделать разницу дат в excel

Применение функции РАЗНДАТ() на практике

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

Но если мне нужен точный возраст, то я записываю более сложную формулу:

=РАЗНДАТ(F2;G2;»y»)&» год «&РАЗНДАТ(F2;G2;»ym»)&» месяцев»

как сделать разницу дат в excel

Усложняет применение RAZNDAT()

В котором функция SENDAT() вызывается дважды за раз, с разными значениями, а слова «год» и «месяц» просто добавляются к результату. Другими словами, истинная сила функции раскрывается только в сочетании с другими функциями MS Excel.

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

как сделать разницу дат в excel

Простой калькулятор расчета баланса рабочего времени

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

Я собираюсь использовать другую функцию (дату today) в качестве первого аргумента вместо ссылки на значение ячейки. TODAY() возвращает сегодняшнюю дату и не имеет никакой другой цели, кроме как вернуть ее.

Читайте так же:
Как изменить количество листов по умолчанию в Excel?

как сделать разницу дат в excel

Вот полнофункциональный калькулятор в MS Excel, учитывающий сегодняшнюю дату.

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

На рабочем листе Excel мы создаем план задач. Сроки выполнения каждой задачи перечислены в одном столбце. Excel требует точной разницы дат для выделения цветом сшитых сроков. Этого можно добиться с помощью условного форматирования и формулы СРЗНАЧ.

Как посчитать разницу между датами в Excel

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

  1. Выделите диапазон ячеек A2:A7 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
  2. Выбираем опцию: «Использовать формулу для определения форматируемых ячеек».
  3. Разница в днях между датами будет вычисляется функцией =РАЗНДАТ(). Для этого В поле ввода введите формулу: =РАЗНДАТ($D$2; C2;»d»)

как сделать разницу дат в excel

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

Расчет количества дней

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

  1. Выберите область листа, в которой необходимо выполнить вычисления. Щелкните правой кнопкой мыши на выделении. Появится всплывающее меню. Выберите «Форматировать ячейку…» в контекстном меню. Вы также можете набрать на клавиатуре Ctrl+1.
  2. Откроется окно форматирования. Если он не открылся на вкладке «Номер», необходимо перейти туда. В блоке параметров «Форматы чисел» установите переключатель в положение «Дата». В правой части окна выберите тип данных, с которыми вы будете работать. Затем, чтобы зафиксировать изменения, нажмите кнопку «OK».

как сделать разницу дат в excel

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

Способ 1: простое вычисление

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

  1. Записываем в отдельные ячейки отформатированного диапазона даты, разность между которыми нужно вычислить.
  2. Выделяем ячейку, в которой будет выводиться результат. В ней должен быть установлен общий формат. Последнее условие очень важно, так как, если в этой ячейке стоит формат даты, то в таком случае и результат будет иметь вид «дд.мм.гг» или другой, соответствующий данному формату, что является некорректным итогом расчетов. Текущий формат ячейки или диапазона можно просмотреть, выделив его во вкладке «Главная». В блоке инструментов «Число» находится поле, в котором отображается данный показатель.

как сделать разницу дат в excel

Если он имеет значение, отличное от «Общий», используйте контекстное меню для запуска окна форматирования, как вы делали в прошлый раз. Установите формат «Общий» на вкладке «Число». Нажмите на кнопку «OK».

как сделать разницу дат в excel

Способ 2: функция РАЗНДАТ

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

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

  • «y» — целые годы;
  • «м» — целые месяцы;
  • «d» — дни;
  • «YM» — разница в месяцах;
  • «MD» — разница в днях (месяцы и годы не учитываются);
  • «YD» — разница в днях (годы не учитываются)

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

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

  1. Напишите формулу для выделенной ячейки в соответствии с синтаксисом, описанным выше, и исходными данными в виде начальной и конечной дат.
  2. Чтобы выполнить расчет, нажмите Enter. Результат отображается в виде числа, указывающего на количество дней между датами в указанной ячейке.

как сделать разницу дат в excel

Способ 3: вычисление количеств рабочих дней

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

Существует разница между этим форматом и всеми ранее рассмотренными. При вычислении количества дней от определенной даты необходимо ввести знак равенства (=) в ячейку, где появится результат, а затем выделить ячейку, откуда будет производиться вычисление. Выделяем ее курсором. Следующим шагом ставим минус (-). После этого формула состоит из половины. Осталось сделать только одно: ввести количество дней, которое вы хотите вычесть. Данные вводятся с помощью клавиатуры компьютера. Затем «Enter», и задача решена. В ячейке находится ожидаемый результат.

Как и в случае с датами, время рассчитывается аналогичным образом. Разделив последнее на предыдущее, мы получим значение времени. Для того чтобы рассчитать прошедшие минуты для временного интервала 22:55 часов и 15:13 часов, мы применим алгоритм в формате «время». Если вы вводите числа точно так же, как данные о времени, то формат автоматически становится форматом времени. В ячейке ожидаемого результата ставим обычное «равно» (=), затем активируем курсором мыши ячейку с самым поздним временем, в нашем случае это ячейка со значением 22:55. Вводим «минус» (-), затем переходим к ячейке с более ранним значением времени, например, 15:13. Полученная формула будет выглядеть как «=C4-E4». При нажатии Enter расчет будет активирован.

Что мы видим на табло? Это совсем не то, что мы ожидали. И это правда. Мы ждем минут — мы получаем часы и минуты. Что вы делаете, чтобы получить результат за считанные минуты? Вы умножаете количество минут на количество часов в сутках. Короче говоря, вы умножаете 60 на 24, что можно сделать, умножив 1440, то есть умножить 7 часов и 42 минуты на 1440. В Excel это делается следующим образом. Откройте ячейку со знаком «равно» (=), щелкнув по ячейке, содержащей данные 7:42. Данные появятся в строке формул,

Затем нажмите на «умножить» (*) и введите значение 1440. Нажмите Enter. Вот и все — смотрите результат.

Опять же, это не то, что мы ожидали. Мы видим 0:00. Это связано с тем, что когда мы ввели коэффициент 1440, формат числа был автоматически изменен на время, что дало нам искаженный результат. Чтобы увидеть искомое значение в минутах, измените формат с «hour» на «numeric» или «total».

Формат изменяется путем активации ячейки и изменения формата в окне меню на «общий». Это изменение формата можно сделать, нажав Ctrl + 1. С помощью этих клавиш мы активируем список форматов. Выберите «General» из списка и нажмите Enter, после чего изменение формата будет завершено.

После вышеописанных манипуляций с форматами и вычислениями, мы получаем желаемый результат 462m.

Как в Экселе вычесть даты и получить дни?

Функция DATAMEST требует два аргумента: начальную дату и количество месяцев для сложения или вычитания. Чтобы вычесть месяцы, введите отрицательное число в качестве второго аргумента. Например, =TIME(«15.09.19»;-5) возвращает 15.04.19.

Как в Excel найти разницу между датами?

  1. Выберите диапазон ячеек A2:A7 и выберите инструмент «Главная» — «Стили» — «Условное форматирование» — «Создать правило». Выберите опцию «Использовать формулу для определения форматируемых ячеек».
  2. Разница в днях между датами будет рассчитана с помощью =RASANDAT().

Как в Excel посчитать период времени?

  1. Введите текущую дату в ячейку A1 на чистом листе бумаги, нажав CTRL+;.
  2. В ячейку A2 введите промежуточный период в днях, например, 127.
  3. В ячейку A3 введите формулу: =A1+A2.

Как в Экселе посчитать разницу между датами в месяцах?

Если вы используете формулу =SEND(A2;B2; «m»), вы получите количество полных месяцев между двумя датами. При расчете стажа мы предполагаем, что человек работал весь месяц — 1 полный месяц.

Как в Excel сделать автозаполнение даты?

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

Как найти разницу между числами в Экселе?

Для этого выделите любую ячейку, поставьте знак ‘=’ (он набирается на клавиатуре), введите первое число, поставьте знак ‘-‘. (оно также вводится), введите второе и так далее для всех вычитаемых значений. В конце нажмите «Enter», и расчет будет завершен.

Как отнять две даты в Excel?

Нетрудно догадаться, что в Excel для вычисления разницы между двумя датами нужно вычесть самую старую дату из самой новой. В ячейку B1 введите формулу:=A3-A1. Вы получите количество дней между двумя датами.

Как в Excel посчитать количество календарных дней между датами?

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

Как вывести дату и месяц Excel?

Это самый очевидный и простой способ. Например: =MONTH(A2) — возвращает месяц даты в ячейке A2. =МЕСЯЦ(ДАТААА(2020,7,15)) — возвращает число 7, соответствующее июлю.

Как изменить дату на месяц в Excel?

В пустой ячейке введите эту формулу = ТЕКСТ (A2; «мммм»), в данном случае в ячейке C2. и нажмите клавишу Enter. Перетащите дескриптор автозаполнения этой ячейки в нужный диапазон. И дата была преобразована в название месяца.

Как перевести дату в число в Excel?

Используя DATAZNACH, вы можете превратить дату, сохраненную в виде текста, в порядковый номер, который Excel может рассматривать как дату. В формуле типа =DATAZNACH(«1.1.2008») число 39 448 будет соответствовать 1 января 2008 года.

Как исключить выходные на оси дат в Excel?

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

Как ввести дату и время в Excel

Что касается российских региональных настроек, то в Excel существует множество способов ввода даты — и он понимает их всех:

Применение дефисов

Использование дробей

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

Каждой ячейке таблицы присваивается время с помощью двоеточия. Например

При желании вы также можете указать количество секунд — опять же, указав их через двоеточие:

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

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

Чтобы ввести в ячейку сегодняшнюю дату, нажмите комбинацию клавиш Ctrl + G.

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

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

Чтобы в ячейке всегда была сегодняшняя дата, следует использовать функцию TODAY (Вставка — меню Функция, категория Дата и время):

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

Вы увидите следующее, если выберете ячейку с датой и установите для нее формат Общий на вкладке Формат — Ячейки — Число — Общий:

Например, вот как это выглядит 8.10.2006 3:42

На самом деле Excel хранит и обрабатывает каждую дату именно таким образом — как число с целой и дробной частью. Целая часть этого числа (38998) — это количество дней с 1 января 1900 года (это точка отсчета) до сегодняшнего дня. Дробная часть (0,65417) — это доля дня (1 день = 1,0).

На основании этих фактов можно сделать два чисто практических вывода:

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

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

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

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

В этом случае возникает больше сложностей. Праздничные дни, субботы и воскресенья должны быть исключены из этого правила. Функция NUMBERBUS, находящаяся в дополнении Analysis Package, является наиболее эффективным методом для расчета.

Первое, что вам нужно сделать, это подключить дополнение Analisys Toolpak. Для этого перейдите в Инструменты — Дополнения и установите флажок Analisys Toolpak. После этого функция NETWORKDAYS появится в мастере функций в категории Дата и время.

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

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

Как правильно его рассчитать, вы можете прочитать здесь.

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

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

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

Эта операция выполняется функцией WORKDAY дополнения Analysis Package. Она позволяет рассчитать дату, которая сдвинута вперед или назад по отношению к начальной дате на нужное количество рабочих дней (с учетом суббот и воскресений и национальных праздников). Использование этой функции точно такое же, как и использование функции CLEARRABDNEY, описанной выше.

Расчет дня недели

Разве вы не родились в понедельник? Нет? Вы уверены? Вы можете легко проверить это с помощью функции WEEKDAY из категории Дата и время.

Другой способ: выделите ячейку с датой, откройте меню Формат — Ячейки, выберите вкладку Числа, затем Все форматы, и в строке Тип введите новый формат DDD

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

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

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

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