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

Обновлено: 21.11.2024

Если вы введете время 12::00::00 (полдень) в любую ячейку, а затем отформатируете его как «Общее», вы увидите, что оно имеет числовое значение 0,5. Точно так же введите время 24::00::00 в любую ячейку. Посмотрите на строку формул, и вы увидите, что Excel показывает 01.01.1900 00:00:00 .

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

Вот три способа заставить Excel отображать отрицательные значения времени.

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

Имейте в виду, что это приведет к тому, что Excel изменит начальные даты, по которым рассчитываются все ячейки, с 1 января 1900 г. на 2 января 1904 г. Изменение этого параметра повлияет только на книгу, в которой вы вносите изменения.

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

Вы получите результат -1:00 , но только в том случае, если вы установили флажок в поле системы дат 1904 года, как описано ранее.

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

Способ 2. Использование функции ТЕКСТ

Второй метод требует использования функции ТЕКСТ. Для начала введите 5:00:00 в ячейку A2. В ячейке A3 введите 6::00::00 утра. В ячейке A4 введите следующее:

С помощью этой вложенной формулы вы вычитаете A3 из A2, чтобы получить положительное значение времени. Затем вы форматируете ячейку с помощью функции ТЕКСТ, чтобы она показывала отрицательное время. Использование комбинации функций MAX и MIN гарантирует, что вы всегда будете вычитать более раннее время из более позднего.

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

Способ 3. Использование пользовательского формата

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

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

сообщить об этом объявлении

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

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

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

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

Загрузите рабочую книгу Excel, чтобы следовать ей.

Отрицательное время в Excel

Для расчета разницы во времени в диапазоне D3:D7 использовалась следующая формула.

Способ 1. Использование системы дат 1904 года

Один из способов – изменить систему дат Excel с 1900 года на систему дат 1904 года.

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

Чтобы изменить систему дат;

Это успешно исправляет отрицательное время в столбце D.

Это отличное решение, если в таблице нет других дат. Если вы это сделаете, существующие значения даты будут изменены на четыре года в будущем.

Способ 2. Использование функции ТЕКСТ

Альтернативный метод – использование формулы. Чтобы отобразить отрицательное время в Excel со знаком минус «-», воспользуемся функцией ТЕКСТ. Эта замечательная функция позволяет нам преобразовывать числа в текст, но при этом применять форматирование чисел.

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

Если результат отрицательный, то функция ТЕКСТ используется вместе с функцией АБС для отображения отрицательного времени в Excel. Функция ABS возвращает абсолютное значение без учета знака.

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

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

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

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

Отображать отрицательное значение времени

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

Отображение отрицательного значения времени с помощью формулы

Вы также можете использовать формулу, основанную на функции Text, функции Max и функции MIN, чтобы получить результат правильного отображения отрицательного значения времени в Excel.

Так же, как эта формула:

Введите эту формулу в пустую ячейку и нажмите клавишу Enter на клавиатуре.

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

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

Примечание: вы должны знать, что возвращаемый результат на самом деле является текстовым значением, а не числовым значением.

Видео: отображение отрицательного значения времени

Связанные функции

Иногда нам может понадобиться ввести последовательность дней в Excel для какой-либо цели, и мы можем нажать Ctrl+; чтобы вставить текущую дату в ячейку, а затем перетащите ячейку вниз, чтобы прикрепить следующие дни к другим ячейкам, затем .

В Excel мы можем правильно ввести дату, если установить формат ячейки «Дата». Если есть список, включающий несколько дат, есть ли способ преобразовать эти даты в дни недели соответственно? На самом деле, есть некоторые.

Иногда мы хотим заполнить 1,2,3,4,5… в каждую ячейку в столбце, для реализации этого мы можем ввести 1 в первую ячейку, а затем перетащить маркер заполнения вниз, чтобы заполнить следующие ячейки. Но если есть тексты .

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

В этом посте рассказывается, как объединить текст и даты в одной ячейке в Excel. Как объединить значения текста и даты в одну ячейку в Excel 2013/2016. Объедините текст и дату в одну ячейку.

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

В этом посте рассказывается, как преобразовать военное время в стандартное с помощью формулы в Excel. Как преобразовать военное время 17:29:30 в обычное время 17:29:30 в Excel. Как перевести стандартное время в военное.

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

В этом посте рассказывается, как преобразовать текущую дату в указанный формат даты в Excel. Как преобразовать дату в формат ГГГГ-ММ-ДД с помощью функции «Формат ячеек» в Excel. Как преобразовать формат даты в определенный .

В этом посте рассказывается, как объединять ячейки и сохранять формат данных в Excel. Как объединить ячейки и сохранить определенный формат даты с формулой в Excel 2010/2013/2016. Объединение ячеек и сохранение формата даты Предполагая, что .

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

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

Базовая система 1900 года начинается с 1 января 1900 года, как день 1. Эта система не позволяет работать с отрицательным форматом времени. Вам нужно создать дополнительные функции для отображения отрицательного времени.
Базовая система 1904 года (также называемая датами и временем Macintosh) начинается с 1 января 1904 года и соответствует номеру дня 0. Эта система работает с отрицательными временами, как с обычными числами. Ниже показано, как переключиться на систему дат 1904 года.

Система дат 1900 года

Excel правильно отображает только положительное время, но не может отображать отрицательное время:

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

Введите формулу:

= ЕСЛИ ( > 0, ТЕКСТ ( , "чч:мм"), "-" и ТЕКСТ (ABS ( ), "чч:мм"))

Функция ЕСЛИ выполняется и возвращает одно значение для результата ИСТИНА и другое для результата ЛОЖЬ: ЕСЛИ ( , [ ], [ ]).
является результатом расчета времени.
"чч:мм" – это формат времени.
& – это операция объединения двух строк.
Функция ТЕКСТ возвращает заданный объект в виде текста: ТЕКСТ ( , ).
Функция ABS возвращает абсолютное значение числа (удаляет знак минус (-) из отрицательного числа): ABS ( ).

Система дат 1904 года

При изменении системы все даты в вашей рабочей тетради будут автоматически изменены: фактически к каждой дате будет добавлена разница между 1 января 1900 года и 2 января 1904 года — 1462 дня. Будут изменены все данные, содержащие формулы, а также диаграммы и диаграммы, если в них используются даты!

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

1. На вкладке "Файл" нажмите кнопку "Параметры":

2. В диалоговом окне "Параметры Excel" на вкладке "Дополнительно" в разделе "При расчете этой книги" выберите параметр "Использовать систему дат 1904 года":

Excel отлично работает с отрицательными значениями времени в системе дат 1904 года:

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

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

Быстрая вставка текущей даты и времени

Как легко изменить формат ячеек

При работе с данными и формулами в Excel одним из важных моментов является формат ячеек. Данные могут быть представлены совершенно по-разному.

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