Ввод отрицательного времени (Microsoft Excel)

Гвидо нужно ввести в клетки отрицательные времена. Если он вводит положительное время (2:00), Excel прекрасно это распознает. Если он попытается ввести -2: 00, Excel сообщит ему об ошибке в его формуле. Гвидо интересуется, как проще всего ввести отрицательное значение времени в ячейку и заставить Excel распознавать его как значение времени.

Прежде чем дать несколько возможных ответов на этот вопрос, полезно понять, как Excel просматривает дату и время. Внутри дата и время хранятся в виде серийных номеров, которые представляют собой не что иное, как реальные числа. Допустим, вы вводите дату в ячейку B4, например «17 марта 2020 г.» (без кавычек. Excel понимает, что вы вводите дату, и преобразует ее в серийный номер. Однако вы не можете сказать, что это серийный номер. , потому что Excel показывает дату, представленную серийным номером (см. рисунок 1.)

Рисунок 1. Ввод даты в Excel.

Обратите внимание, что дата отображается в ячейке как 17 марта-20, а в строке формул — как 17 марта 2020 г. (Точное отображение даты в Excel зависит от того, как вы ввели ее, и какие региональные настройки находятся в пределах Windows.) Дата, показанная в ячейке (17 марта 20), является результатом того, как отформатирована ячейка. Когда вы вводили дату в ячейку и поскольку ячейка ранее имела формат «Общий», Excel изменил форматирование ячейки, чтобы отображать дату так, как вам удобно.

Из-за даты, отображаемой в строке формул (17.03.2020), вы можете подумать, что это Это способ, которым Excel хранит даты внутри себя. Не так; вы можете увидеть это, введя формулу в соседнюю ячейку (= B4) и отформатировав эту ячейку как «Общие». (См. Рис. 2.)

Рисунок 2. Как Excel сохраняет дату.

На панели формул отображается формула (= B4), как и должно быть, но в ячейке отображается значение 43907. Если вы отформатировали дату в ячейке B4 как общую, она также отобразит то же значение. Это серийный номер; так Excel хранит даты внутри себя.

Но что означает серийный номер? Это «счетчик» количества дней, прошедших с базовой даты. Обычно эта базовая дата — 1 января 1900 года, а этот день — день 1. 2 января 1900 года будет днем ​​2, и так далее. Таким образом, 43907 — это 43907 день, начинающийся с 1 января 1900 года.

Однако возможно, что базовая дата для книги будет другой. В исторических целях Excel понимает две возможные базовые даты. Другой называется системой дат 1904 года. Он начинает отсчет с 1 января 1904 года. В отличие от системы дат 1900 года, система дат 1904 года начинает отсчет с 0. Таким образом, 1 января 1904 года имеет порядковый номер 0, 2 января 1904 года имеет порядковый номер 1, и т. д.

Вы можете увидеть, какая система дат используется в книге, выполнив следующие действия:

(См. Рис. 3.)

Если флажок установлен на шаге 4, то ваша книга использует систему дат 1904 года. Если он не выбран, значит, вы используете систему дат 1900.

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

Если вы вводите дату со временем в ячейку, например «17.03.20 18:00»

(снова без кавычек, тогда он сохраняется внутри как часть серийного номера справа от десятичной точки. (См. рисунок 4.)

Рисунок 4 . Просмотр того, как Excel сохраняет дату и время.

Дробная часть, сохраненная с серийным номером (0,75), означает, что введенное вами время (18:00) составляет 75% в течение дня . Время — десятичная часть — может находиться в диапазоне от 0,0 (12:00:00 утра) до 0,999988426 (11:59:59 вечера).

Из-за такой схемы хранения дат и раз внутри как серийные номера, в мире Excel не существует такой вещи, как «отрицательное время».

Невозможно проанализировать и сохранить такое значение, поэтому Excel выдает сообщение об ошибке при попытке. может выполнять «математические вычисления даты» и вычитать время из значения времени, уже сохраненного в ячейке, но все, что вы делаете, это настраиваете серийный номер даты и времени. Например, если у вас есть серийный номер 43907.75, хранящийся в ячейке, и s Уберите из него «время», которое дало бы отрицательный результат, все, что вы сделали, это переместились в предыдущий день. (См. Рисунок 5.)

Рисунок 5. Вычитание из даты и времени.

Обратите внимание, что в ячейку B6 было введено время без даты (22:00). Это было проанализировано Excel как серийный номер с «нулевой датой», как показано в ячейке C6. Если вы попытаетесь вычесть время из серийного номера, дающего отрицательный результат, Excel не сможет отобразить результат как время. (См. Рис. 6.)

Рис. 6. Вы не можете слишком долго вычитать время.

Если навести указатель мыши на решетки, показанные в ячейке B9, вы увидите сообщение об ошибке: «Отрицательные или слишком большие даты и время отображаются как ». (Точная формулировка сообщения об ошибке может отличаться от версии к версии Excel.)

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

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

Так что же делать Гвидо? Если ему действительно нужно работать с отрицательными временами, он мало что может сделать по уже объясненным причинам.

(Впрочем, подробнее об этом чуть позже.) Если он просто хочет вычесть время, это другая история. Тот факт, что Гвидо пытается ввести что-то вроде -02: 00, указывает на то, что он работает с продолжительностью событий, а не с фактическими «часами». (Помните, что Excel работает с часами для анализа, а -02: 00 — это не время часов — вы не можете сказать кому-то, что встретитесь с ними в -02: 00.)

Чтобы справиться с длительностью событий, вы можете просто позволить Excel вычислить за вас. Например, время начала может быть помещено в ячейку B4, а количество часов, которое Гвидо хочет вычесть из него (2), может быть помещено в ячейку C4. Затем в ячейке D4 он мог использовать формулу для вычисления результата:

Разделив количество часов на 24, вы получите правильное десятичное значение, которое используется внутри Excel в своих серийных номерах. Конечно, если результат формулы дает отрицательный серийный номер, вы получите отметки решетки, когда попытаетесь отформатировать результат как время. Единственное решение — убедиться, что то, что хранится в ячейке B4, включает дату. Вы можете отформатировать ячейку B4 так, чтобы отображалось только время, но включение даты предотвратит появление ошибки. Вы можете узнать больше о работе с отрицательным истекшим временем, взглянув на этот ExcelTip:

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

Например, посмотрите на результаты вычитаний при использовании системы дат 1904 года. (См. Рисунок 7.)

Рисунок 7. Система дат 1904 года обрабатывает отрицательные значения времени.

Этот подход отлично подойдет для отдельных рабочих книг в вашей собственной системе, но может оказаться не таким эффективным, когда другие будут использовать вашу книгу. Опираясь на рабочие тетради, в которых используются разные системы дат (одни 1900, другие 1904)

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

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

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13356) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.