Auditportal.ru

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

Как извлечь миллисекунды из времени в Excel?

Excel works!

menu

Еще раз привет. Как обычно начну статью с того, что скажу — часто встречаюсь с каким-нибудь незнанием со стороны пользователей и сейчас расскажу об этом. Банально, но действительно так и приходят идеи о статье. Сегодня хочется написать отдельный текст про работу со временем в Excel. Как можно быстро обработать временные данные? Как перевести время в число в Excel? Как не запутаться в этом — читаем в тексте!

перевести время в число в Excel

Время и число в Excel. =СЕГОДНЯ()

Сразу скажу что дата и время в Excel — это в первую очередь числовые данные. И только в определенном формате эти числа выглядят как время или дата.

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

Любое значение дата_время в Excel — это число преобразованное в нужный формат числа при помощи опции Формат ячеек!

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

Время дата1

Как это повлияет на числовые данные, если они преобразуются? Число отображается как 42544.00.

В формате времени время будет равно, например, 21:00, как показано на первом изображении. Значения чисел идентичны.

Итак, как складывать и вычитать такие данные? Так же, как и обычные числа! Чтобы отобразить дату_время в ячейке, вам нужно сложить числа и выбрать формат. Как в примере:

перевести время в число в Excel 1

То же самое касается вычитания данных друг из друга.

Теперь вы можете перейти к советам по работе с датой и временем.

Отделить дату от времени и наоборот в Excel

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

Сначала проверьте, что это число. Затем вы можете предпринять следующие действия. В противном случае следуйте инструкциям в статье.

Используйте формулу для определения времени от date_time

Формула находит целое значение числа, что будет для нас датой и вычитает из общего значения — разница будет для нас временем, не забудьте перевести в полученные данные в формат времени.

Время дата5

Как перевести время в число в Excel? Функции ДЕНЬ, ЧАС, МИНУТЫ

Это тоже просто, если знать хитрость. Мы предпочитаем чаще использовать формат 3,5 часа, чем 3:30. Чтобы преобразовать число в формате времени в число с нужным значением. В Excel есть отличные функции для расчета времени, такие как =Час(), =Минуты().

Читайте так же:
Как извлечь все, кроме первого / последнего слова в Excel?

Для преобразования 3:30 в три с половиной часа требуется формула.

Как это работает: мы находим количество полных часов с помощью функции HOUR, затем находим количество полных минут и делим его на 60, чтобы получить количество минут в часе. Готово!

Так же, как верно подсказали мне в комментариях, можно умножить 3:30 (в ячейке A1) на 24 — получится числовое значение времени.

Если вы хотите посчитать количество дней во времени (например, 33:30:00), необходимо воспользоваться функцией DAY.

Важно! Не забудьте перевести ячейку в числовой формат, иначе после выполнения формулы, или в ячейке будет выдаваться 0:00:00
Это будет удобно при работе с большим объемом данных, вычислениями со временем или группировкой данных в сводной таблице

Как в Excel вычитать числа

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

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

  • Вычитание двух дат в Excel
  • Как добавить (вычесть) дни к дате в Excel
  • Как добавить (вычесть) недели к дате
  • Добавление месяцев к дате в Excel
  • Добавление лет к дате в Excel
  • Добавление (вычитание) дней, месяцев и лет к дате
  • Как добавить и вычесть время в Excel?

Как вычитать даты в Excel

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

Пример 1. Непосредственно вычитаем одну дату из другой

Наверняка вы знаете, что в Excel даты хранятся как целые числа, начиная с 1, что соответствует 1 января 1900 года. Чтобы вычесть одно число из другого, просто используйте арифметические действия:

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

Отрицательные числа возвращаются путем вычитания, когда вы вычитаете более позднюю дату (6 мая 2015 года) из более ранней (1 мая 2015 года). Однако из-за синтаксиса функции DATEDIF начальная дата не может быть больше конечной и возвращает ошибку.

Читайте так же:
Как изменить цвет зачеркивания без изменения цвета текста в Excel?

Сложение и вычитание дат в Excel

Пример 3. Вычитаем дату из текущей даты

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

Сложение и вычитание дат в Excel

Пример 4. Вычитание дат с помощью функции ДАТА

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

Функция DATE имеет следующий синтаксис: DATE(год; месяц; день).

Подведем итоги. В Excel имеет смысл выполнять самый простой и очевидный способ вычитания дат — вычитать одну дату из другой.

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

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

Как прибавить (вычесть) дни к дате в Excel

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

Пример 1. Прибавление дней к дате в Excel

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

    Cсылкой на ячейку:

На следующем рисунке объясняется, как работают эти формулы. На момент написания статьи текущая дата была 6 мая 2015 года.

Сложение и вычитание дат в Excel

Важно отметить, что в результате применения этих формул дата представляется целым числом. Вы можете отобразить ее в виде даты, выделив ячейку (или ячейки) и нажав Ctrl+1. Откроется диалоговое окно Формат ячеек. На вкладке Число из списка числовых форматов выберите Дата, а затем укажите нужный формат. Дополнительные сведения см. в разделе Как изменить формат даты в Excel.

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

=A2-10 =ДАТА(2015;5;6)-10 =СЕГОДНЯ()-10

Как прибавить (вычесть) несколько недель к дате

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

    Прибавляем N недель к дате в Excel:

= A2 + N недель * 7

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

= А2 — N недель * 7

Вычтите 2 недели из сегодняшней даты, используя данную формулу:

Как прибавить (вычесть) несколько месяцев к дате в Excel

Чтобы прибавить (или вычесть) определенное количество месяцев к дате, используйте функцию DATE или EDATE, как показано ниже.

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

Пример 1. Прибавляем несколько месяцев к дате с помощью функции ДАТА

Например, если у вас есть список дат в столбце A, вы можете захотеть добавить положительные месяцы (положительное число) или вычесть отрицательные месяцы (отрицательное число) в какой-нибудь ячейке, скажем, в C2.

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

Эта формула должна что-то сделать, давайте посмотрим. Эта формула имеет четкую и очевидную логику. Она называется DATE(год, месяц, день), когда предоставляется один из следующих аргументов:

  • Год из даты в ячейке A2;
  • Месяц из даты в ячейке A2 + количество месяцев, указанных в ячейке C2;
  • День из даты в ячейке A2 ;

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

Сложение и вычитание дат в Excel

Естественно, ничто не мешает ввести в формулу минус справа, чтобы вычесть месяцы:

    Прибавить месяцы к дате:

Пример 2. Прибавляем или вычитаем месяцы из даты с помощью функции ДАТАМЕС

В Excel есть специальная функция, которая возвращает дату назад или вперед на определенное количество месяцев — это функция DATAMES (EDATE). Он доступен в последних версиях Excel 2007, 2010, 2013 и новой Excel 2016.

При использовании DATAMES (EDATE) необходимо указать следующие два аргумента:

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

В предыдущем примере эти Формулы с DATE дали бы следующий результат:

Сложение и вычитание дат в Excel

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

    Эта формула прибавляет 10 месяцев к 7 мая 2015 года

Примечание: Функция EDATE просто возвращает целое число. Чтобы представить его как дату, необходимо применить к ячейке форматирование даты. Как это сделать, описано в разделе Как изменить формат даты в Excel.

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

    Прибавляем 5 лет к дате, указанной в ячейке A2:

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

Читайте так же:
Как извлечь первые или первые два слова из текстовых строк в листе Google?

Сложение и вычитание дат в Excel

Прибавление (вычитание) дней, месяцев и лет к дате

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

    Для прибавления X лет, Y месяцев и Z дней:

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

Как складывать и вычитать время в Excel

В Microsoft Excel можно складывать и вычитать время с помощью функции ВРЕМЯ. При использовании функции ДАТА можно рассматривать единицы времени (часы, минуты и секунды) как годы, месяцы и дни.

    Прибавить время в Excel:

Где A2 — ячейка, содержащая изменяемое время.

Например, чтобы добавить 2 часа 30 минут и 15 секунд к времени в ячейке A2, нужно использовать следующую формулу:

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

Сложение и вычитание дат в Excel

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

Как в Excel вычислить часы между двумя значениями времени

Как в Excel вычислить часы между двумя значениями времени

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

Основная формула

Описание

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

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

Пояснение

Трудно рассчитать время между двумя точками во времени, когда полночь пересекает полночь. Причина в том, что время, пересекающее полночь, часто имеет время начала позже времени окончания (т.е. начало в 21:00, конец в 6:00). Исходя из этой ситуации, в статье представлено несколько решений.

Простой расчет продолжительности

Когда время начала и время окончания совпадают в один и тот же день, легко рассчитать продолжительность в часах. Если время начала работы — 9:00, а время окончания — 17:00, вам понадобится только эта формула:

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

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

Как Excel отслеживает время

В Excel день равен 1, что соответствует 24 часам. Это означает, что время и часы являются долями единицы, как показано в таблице ниже:

Читайте так же:
Как импортировать несколько текстовых файлов на несколько листов?
ЧасыВремяДоляЗначение
33:003/240,125
66:006/240,25
44:004/240,167
88:008/240,333
1212:0012/240,5
1818:0018/240,75
2121:0021/240,875

Когда наступает полночь

Вычислить прошедшее время сложнее, если время пересекает границу суток (полночь). Например, если в один день время начала — 22:00, а время окончания — 5:00 утра следующего дня, то время окончания на самом деле меньше, чем время начала, и приведенная выше формула вернет отрицательное значение, а Excel отобразит строку хеш-символов (########).
Чтобы исправить эту проблему, вы можете использовать эту формулу для времени, которое проходит границу суток:

Количество времени в день 1 получается путем вычитания времени начала из 1. Вы можете просто добавить это время к времени дня 2, которое совпадает с временем окончания. Поскольку эта формула не работает для случаев, которые происходят в один и тот же день, нам необходимо использовать функцию ЕСЛИ:

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

Альтернатива c функцией ОСТАТ

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

Эта формула учитывает отрицательное время, используя функцию ОСТАТ, чтобы «перевернуть» отрицательное значение на требуемое положительное значение. Поскольку эта формула обрабатывает время в один и тот же день и время, после полуночи, то нам не нужен условный оператор ЕСЛИ.
Примечание: ни одна из приведенных выше формул не будет обрабатывать длительность более 24 часов.
Если вам это нужно, см. параметр «дата + время» ниже.

Упрощение с помощью даты + времени

Вы можете просто решить задачу радикального вычисления прошедшего времени, используя значения, содержащие как дату, так и время. Чтобы ввести вместе дату и время, используйте единый интервал между датой и временем.
Например. Если вы отформатируете эту дату 01.04.2020 10:00 в основном формате, вы увидите такое значение:

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

Excel упрощенный расчет часов между двумя значениями времени
Результат отформатирован в пользовательском формате: [h]:mm для отображения прошедших часов. Эта формула правильно рассчитает часы между двумя временными метками за один день или за несколько дней.
Формат времени [h]:mm

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