Как в экселе отобразить отрицательное время

Обновлено: 06.07.2024

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

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

Возвращает заданную дату в числовом формате.

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

Преобразует дату из текстового формата в числовой.

Преобразует дату в числовом формате в день месяца.

ДНИ

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

Вычисляет количество дней между двумя датами на основе 360-дневного года.

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

Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.

Преобразует дату в числовом формате в часы.

НОМНЕДЕЛИ.ISO

Возвращает номер недели по ISO для заданной даты.

Преобразует дату в числовом формате в минуты.

Преобразует дату в числовом формате в месяцы.

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

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

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

Возвращает текущую дату и время в числовом формате.

Преобразует дату в числовом формате в секунды.

Возвращает заданное время в числовом формате.

Преобразует время из текстового формата в числовой.

Возвращает текущую дату в числовом формате.

Преобразует дату в числовом формате в день недели.

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

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

РАБДЕНЬ.МЕЖД

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

Преобразует дату в числовом формате в год.

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

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

Для Excel даты и время — это не что иное, как числа. В случае даты он по умолчанию считает, что у 1 января 1900 года числовое значение равно 1, у 2 января 1900 года — 2 и т. д. Время считается как десятичные доли, кроме полуночи, числовое значение которой равно 1. (Подробнее об этом — в разделе «Трюк № 38. Трюки с возможностями даты и времени в Excel».) Из-за этого в Excel возникает настоящая проблема с отрицательными значениями времени. Вот три способа заставить Excel отображать отрицательные значения времени.

Способ 1. Изменение системы дат Excel по умолчанию

Первый быстрый и простой способ научить Excel отображать отрицательные значения времени — выбрать команду пункт меню Файл → Параметры Excel → Вычисления (File → Parametres Excel → Calculation) и установить флажок Система дат 1904 (1904 date system). Система дат 1904 называется также датой и временем Macintosh.

Помните, что при этом в Excel изменятся начальные даты, от которых отсчитывается значение всех ячеек. Вместо 1 января 1900 года это будет 2 января 1904 года. Изменение этого параметра влияет только на ту рабочую книгу, в которую вы вносите изменения.
Теперь можно вычитать значения времени и видеть на рабочем листе отрицательные значения времени.

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

Способ 2. Функция ТЕКСТ (TEXT)

Второй способ требует использования функции ТЕКСТ (TEXT). Для начала в ячейке А2 введите 5:00:00 AM, в ячейке A3 — 6:00:00 AM. В ячейке А4 введите следующую формулу: =ТЕХТ(МАХ($А$2:$А$3)-MIN($А$2:$А$3);"-Н::ММ") , в русской версии Excel =ТЕКСТ(МАКС($А$2:$А$3)-МИН($А$2:$А$3);"-Ч::ММ") .

Используя эту вложенную формулу, вы вычтете A3 из А2, чтобы получить положительное значение времени. Затем отформатируете ячейку при помощи функции ТЕКСТ (TEXT), чтобы она могла отображать отрицательные значения времени. Комбинация функций МАКС (МАХ) и МИН (MIN) гарантирует, что более ранее время всегда будет вычитаться из более позднего.

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

Способ 3. Пользовательский формат

Еще один способ отображения отрицательных значений времени без перехода к системе дат 1904 и с гарантированным возвращением числового значения — это использование пользовательского формата -ч:мм (-h:mm) в диалоговом окне Главная → Формат → Формат ячеек (Home → Format → Format Cells).

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

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

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

Добавить или вычесть часы из поля даты / времени с помощью формул

Вы можете применить следующие формулы для добавления или вычитания часов из поля даты / времени в Excel.

Добавить часы в поле даты / времени

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

1. Выберите пустую ячейку, в которой написано ячейка C2, введите формулу = A2 + 12/24 в строку формул, а затем нажмите Enter ключ.

Внимание: В формуле A2 содержит поле даты / времени, 12 - это часы, которые необходимо добавить в поле даты / времени. Вы можете изменить их по своему усмотрению.

Затем вы получите окончательную дату и время после добавления определенных часов в поле даты / времени.

Вычтите часы из поля даты / времени

Чтобы вычесть 12 часов из поля даты / времени, вы можете применить эту формулу = A2-12 / 24 . Смотрите скриншот:

Добавьте или вычтите часы из поля даты / времени с помощью Kutools for Excel

Здесь рекомендую Добавить часы к дате полезности Kutools for Excel. Эта функция помогает легко добавлять или вычитать часы из даты / времени одним щелчком мыши.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выберите пустую ячейку для поиска результата, затем щелкните Kutools > Формула Помощник > Формула Помощник. Смотрите скриншот:

2. в Помощник по формулам диалоговое окно, вам необходимо:

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

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

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

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

Итоговое значение вычисляется в ячейке В8 с помощью следующей формулы (ячейка отформатирована с помощью числового формата [ч]:мм (см. статью 59 для получения более подробной информации)): =СУММ(В2:В6) . Ячейка В9 использует следующую формулу для расчета времени, которое осталось отработать для завершения 40-часовой рабочей недели: =(40/24)-В8 .

Рис. 94.1. Ячейка В8 показывает оставшееся время 40-часовой рабочей недели

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

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

Одно из решений заключается в применении дополнительной системы дат 1904, которая задает серийный номер 1 дате 2 января 1904 года. Выберите Файл ► Параметры, чтобы открыть окно Параметры Excel. Перейдите в раздел Дополнительно и в области При пересчете этой книги установите флажок Использовать систему дат 1904. Отрицательные значения времени сейчас будут выводиться правильно, как показано на рис. 94.3.

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

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

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