Как правильно отображать / показывать отрицательное время в Excel?
Работа с расчетами времени в Excel может быть сложной, когда нужно вычесть более позднее время из более раннего. Например, если вы вычтете 12:20 из 10:15, Excel вернет ошибку ###### вместо отрицательного значения времени, как показано на скриншотах ниже. Эта проблема часто возникает потому, что Excel по умолчанию поддерживает только положительные значения времени в своей стандартной системе дат. Решение этой проблемы важно для точного планирования, управления табелями учета рабочего времени и любых ситуаций, где нужно отразить отрицательные временные интервалы или расчеты сверхурочных.
➤ Правильное отображение отрицательного времени путем изменения системы дат Excel по умолчанию
➤ Отображение отрицательного времени с помощью формул
➤ Отображение отрицательного времени с использованием VBA-кода (пользовательский текстовый формат)
Правильное отображение отрицательного времени путем изменения системы дат Excel по умолчанию
При работе с временными расчетами один из простых способов отобразить отрицательные значения времени — переключить книгу на использование системы дат 1904 года. Это позволит отображать отрицательные результаты корректно (например, «-2:05») вместо ошибок. Этот метод удобен, если вам часто нужно отображать отрицательные временные интервалы, такие как при отслеживании проектов или планировании смен.
Примечания:
- Переход на систему дат 1904 года изменит все существующие даты в книге примерно на 1462 дня (~4 года). После изменения проверьте другие поля дат.
- Эта настройка специфична для книги (она не влияет на другие файлы).
- При совместной работе учитывайте, что пользователи на стандартной системе дат 1900 года могут видеть разные результаты, если книги смешиваются.
- Вы не можете использовать обе системы дат — 1900 и 1904 года — одновременно в одной книге.
1. Откройте диалоговое окно параметров Excel. В Excel 2010/2013 перейдите к Файл > Параметры. В Excel 2007 щелкните кнопку Office и выберите Параметры Excel.
2. В параметрах Excel нажмите Дополнительно. В разделе При расчете этой книги отметьте Использовать систему дат 1904 года. См. скриншот ниже:
3. Нажмите ОК. Теперь отрицательные временные разницы будут отображаться корректно, как показано здесь:
Советы:
- Если сохранение исторической точности дат критично, рассмотрите альтернативные подходы (например, пользовательский текстовый вывод через формулу или VBA), вместо изменения системы дат.
- Задокументируйте изменение системы дат в книге (например, на листе «ReadMe»), чтобы предотвратить путаницу для других пользователей.
Раскройте магию Excel с Kutools AI
- Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
- Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
- Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
- Интерпретация формул: Легко разбирайтесь в сложных формулах.
- Перевод текста: Преодолейте языковые барьеры в ваших таблицах.
Отображение отрицательного времени с помощью формул
Если ваш рабочий процесс не позволяет изменять систему дат (из-за вопросов совместимости или отчетности), вы можете добиться правильного отображения отрицательного времени с помощью формул. Этот метод отображает отрицательные длительности как текст, позволяя получать четкие визуальные результаты без вызова ошибки Excel. Этот подход широко используется в таких сценариях, как расчет дефицита рабочих часов, отслеживание превышений сроков проектов или сравнение прошедшего времени.
1. Введите времена, которые вы хотите рассчитать, в две отдельные ячейки (например, A1 и A2). В пустой ячейке введите следующую формулу для расчета и форматирования отрицательной разницы:
=TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"-H::MM")Здесь A1 и A2 представляют два значения времени. Настройте ссылки на ячейки в соответствии с вашими данными. Эта формула возвращает результат в виде текста, поэтому вы можете видеть отрицательные знаки, даже если Excel обычно возвращает ошибку при вычислении.
2. Нажмите Enter. Результат появится как отформатированная временная строка с минусом, если она отрицательная, как показано ниже:
Альтернативная формула:
Вы также можете использовать эту формулу, которая условно отображает минус, когда результат отрицательный:
=IF(A2-A1<0, "-" & TEXT(ABS(A2-A1),"hh:mm"), A2-A1)В этой конфигурации A2 — это меньшее или более раннее время, а A1 — более позднее время, но вы должны настроить их соответствующим образом для направления ваших расчетов. Этот метод создает текстовый результат для отрицательных значений, что упрощает отчетность, но имейте в виду, что результирующие значения нельзя суммировать как время без дополнительного преобразования.
Примечания:
- Результаты являются текстом, а не истинными значениями времени, поэтому дальнейшие расчеты времени не будут работать, если вы не преобразуете вывод обратно в формат времени.
- Подход с использованием формул не зависит от книги, полезен для обмена и избегает конфликтов глобальных настроек.
- Будьте внимательны при указании правильных направлений ссылок на ячейки, чтобы избежать логических ошибок (например, вычитание начала из конца для получения отрицательного значения).
Отображение отрицательного времени с использованием кода VBA (пользовательский текстовый формат)
Еще один эффективный метод обработки отрицательного времени в Excel — использование макроса VBA. Это особенно полезно, если вы хотите автоматически обнаруживать и отображать отрицательные временные расчеты в вашем листе — даже для больших групп ячеек — без изменения системных настроек или использования формул для каждой ячейки. Этот метод подходит для продвинутых пользователей, управляющих постоянным вводом данных о времени, таких как журналы смен сотрудников, и предотвращает ошибку ###### путем преобразования отрицательных временных результатов в пользовательские текстовые строки (например, с префиксом минуса).
Преимущества: Позволяет выполнять пакетную обработку выбранных ячеек, сохраняет исходные данные, может форматировать результаты по необходимости и не влияет на настройки системы дат Excel.
Недостатки: Требуется включение макросов; не подходит, если вы хотите сохранить собственные значения времени Excel для расчетов за пределами отображения.
Осторожность и советы:
- Всегда сохраняйте резервную копию файла перед запуском VBA-скриптов.
- Это решение преобразует отрицательные временные результаты в текст. Если потребуются будущие расчеты, используйте исходные числовые данные, а не результат отображения, созданный макросом.
- Макросы могут быть отключены вашими IT-политиками. Включите макросы в Excel, чтобы использовать это решение.
1. В Excel нажмите Разработчик > Visual Basic. В окне редактора VBA нажмите Вставить > Модуль, затем вставьте следующий код в окно модуля:
Sub DisplayNegativeTimeAsText()
Dim WorkRng As Range
Dim Cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select time calculation result cells", xTitleId, WorkRng.Address, Type:=8)
For Each Cell In WorkRng
If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
If InStr(Cell.NumberFormat, ":") > 0 Then
If Cell.Value < 0 Then
Cell.NumberFormat = "@"
Cell.Value = "-" & Format(Abs(Cell.Value), "h:mm")
End If
End If
End If
Next
End Sub2. Для выполнения макроса нажмите 
Выполнить кнопку в редакторе VBA или нажмите F5. Появится диалоговое окно, позволяющее выбрать диапазон ячеек, содержащих результаты разницы во времени (даже те, которые показывают ###### ошибку).
Макрос автоматически просканирует выбранный диапазон. Для любой ячейки, содержащей отрицательное значение времени (которое обычно показывает ######), он изменяет ячейку на текстовый формат и преобразует значение в читаемый формат, такой как «-2:05». Положительные временные результаты остаются без изменений. Если вы хотите вернуться к нормальному формату времени, необходимо восстановить исходные значения ячеек или использовать действие отмены/восстановления.
Устранение неполадок и дополнительные советы:
- Если вы выберете диапазон без временных различий (например, пустые ячейки или не временные значения), макрос безопасно пропустит их.
- Если ячейки не выбраны, макрос запросит диапазон и не повлияет на другие данные.
- Всегда проверяйте форматирование после выполнения; затронутые ячейки теперь являются текстом, поэтому исходные временные расчеты не будут работать до сброса.
- Чтобы расширить или адаптировать макрос для других форматов отображения или более длинных структур час-минута-секунда, измените секцию
Format(Abs(Cell.Value), "h:mm")по мере необходимости.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек