Excel - извлекать часы / минуты из времени более 24 часов

1154
Hanan Cohen

Отображаемое значение в A1 составляет 177: 53: 00

Пользовательский формат [ч]: мм: сс

Значение, когда я нажимаю A1, равно 1900-01-07 9:53:00 AM

Мне нужно извлечь часы (177) из клетки в другую камеру

Не могу найти формулу для этого.

Спасибо!

0

4 ответа на вопрос

1
Aganju

Просто используйте = INT (A1 * 24).

Excel хранит даты и время внутри, в днях . Умножение на 24 дает вам часы, а INT обрезает минуты и секунды.

1
robinCTS

Как правильно указал Акина, из-за неточностей, присущих числам с плавающей запятой, которые Excel использует для хранения значения серийной даты, INT()функция не будет работать правильно, чтобы извлечь часы после умножения их на 24.

Тем не менее, эта функция может быть использована для извлечения количества дней, а в сочетании с HOUR()функцией, которая делает правильно извлекать количество часов из дробной части последовательного значения даты, мы получаем следующую формулу:

=HOUR(A1)+INT(A1)*24

Как пояснил Аганью, целочисленная часть значения серийной даты представляет дни, поэтому умножение на нее 24даст количество часов значения серийной даты за целые дни. Добавление количества часов из-за неполного дня дает нам общее количество часов.

@ fixer1234 * Я не понимаю, почему простое решение Аганью было бы неточным. * Смотрите мой первый комментарий к моему ответу. Akina 6 лет назад 0
@Akina и robinCTS, функции времени и форматирование времени от секунд до целых значений. Если вы занимаетесь математикой времени и хотите извлечь отдельные компоненты времени, это округление даст неправильный ответ. В реальной жизни у вас нет часа, пока не пройдут полные 3600 секунд. До этого у вас есть минуты, секунды, миллисекунды и т. Д., Которые составляют менее часа. Excel работает до следующего часа, когда у вас меньше половины следующего часа. Это неправильный ответ. fixer1234 6 лет назад 0
Кстати, функции времени не могут быть более точными, чем значения с плавающей запятой, потому что это то, что они используют для расчета. Погрешность с плавающей запятой в 100 000 раз точнее, чем функции времени, которые вносят погрешность в 1/2 секунды. fixer1234 6 лет назад 0
@ fixer1234 Знаете, когда я писал этот бит, в котором говорилось, что неточность с плавающей запятой была проблемой, что-то в глубине души меня мучало по этому поводу. Я отмахнулся от этого, но сделал мысленную пометку для дальнейшего расследования (－‸ლ) Должен был выслушать. Вы абсолютно правы - это как минимум пять порядков от возможной причины! Спасибо за то, что обратили мое внимание на ½ секунды. Я не знал об этом. robinCTS 6 лет назад 0
0
Akina 
=LEFT(TEXT(A1,"[h]:mm:ss"),FIND(":",TEXT(A1,"[h]:mm:ss"))-1)
Можете ли вы объяснить выше? :) bertieb 6 лет назад 0
Это большой обход - просто используйте `= INT (A1 * 24)`. Превосходные даты и время внутри страны в днях. Умножение на 24 дает вам часы, а INT удаляет минуты и секунды. Aganju 6 лет назад 3
@Aganju Нет, ваше решение может дать неправильный результат. Например, проверьте обе формулы с помощью «A1 = 1.041661». Akina 6 лет назад 1
@ Акина, твоя формула вызвала # Значение ошибки, просто проверь! Rajesh S 6 лет назад 0
@Aganju, да твоя формула набирает 177, опубликуй как ответ! Rajesh S 6 лет назад 0
@RajeshS Какой тип и значение вы помещаете в ячейку A1? Проверь это**. А какой локал вы используете? проверить строку формата данных, она должна быть локальной ... Akina 6 лет назад 0
Точно так, как показано на OP. Rajesh S 6 лет назад 0
@RajeshS Эта формула дает правильный результат на моей рабочей станции после редактирования строки формата данных для национальной. Делать то же самое. Akina 6 лет назад 0
@RajeshS Формула была проверена в GoogleSpreadsheet. См. Https://docs.google.com/spreadsheets/d/1nyxobV_6GlYfw_UG18ZvR5i89bk1fE_X-E2BOJ9NyqY/edit?usp=sharing - результат верный. Проверь себя. Akina 6 лет назад 0
@RajeshS, я также проверил формулу на LO Calc, и она работает. Если вы получаете сообщение об ошибке, я подозреваю, что ваше значение рассматривается как текст (возможно, проблема с локалью). fixer1234 6 лет назад 0
@ fixer1234, возможно, что введенное мной значение точно такое же, как показано на OP, и формат даты настраивается в соответствии с датой и временем, а мои региональные настройки для регионального языка - "US", формат клавиатуры - также "US", по умолчанию дата `MM / DD / YYYY` &` (UTC + 05: 30) Ченнай, Калькутта, Мумбаи, Нью-Дель` - часовой пояс. Rajesh S 6 лет назад 0
@RajeshS * введенное мной значение точно такое, как показано в OP *. Введите значение `7.4118` и затем отформатируйте его как прошедшее время (` [ч]: мм: сс`). Akina 6 лет назад 0
@RajeshS, все должно быть в запястье. :-) Странно, что это не работает на вашем компьютере. Акина, я попробовал твой пример A1 = 1.041661 (вызов Аганью). Интересно. Мне кажется, что функции Excel и алгоритмы форматирования ошибочны. Они округляются, когда разница невелика. Если цель состоит в том, чтобы извлечь единицы времени, округление даст неправильный ответ. fixer1234 6 лет назад 0
@ fixer1234 * похоже, что функции Excel и алгоритмы форматирования ошибочны * Нет. Формула просит обрабатывать значение как число - и Excel обрабатывает его как число с общей погрешностью значения с плавающей запятой. По этой причине я использую функции, которые обрабатывают начальное значение как временное - с точностью. Неявный кастинг - это зло. Akina 6 лет назад 0
Ваша формула округляется и выдает неправильный ответ для 1.041661. Результат должен быть 24 часа + смена. Это не становится 25 часов до значения времени 1.0416666667. Это не из-за неточности с плавающей запятой, потому что разница намного больше, чем это. Это потому, что Excel округляется до ближайшей секунды. Вы не можете полагаться на те функции времени или форматирование времени для математики времени, которая должна быть точной. fixer1234 6 лет назад 0
0
Hanan Cohen

Вернулся к нему через некоторое время. После ответа @Akina моя окончательная формула

= INT (ВЛЕВО (ТЕКСТ (А1, "[ч]: мм: сс"), НАЙТИ (":", ТЕКСТ (А1, "[ч]: мм: сс")) - 1)) + (INT (MID (ТЕКСТ (А1, "[ч]: мм: сс"), FIND ( ":", TEXT (A1, "[ч]: мм: сс")) + 1,2)) / 60)

1900-01-07 9:53:00 AM

Который отображается как

177: 53: 00

возвращается как

177,88

Это очень запутанный способ получить неправильный ответ (он будет неправильным, когда вы близки к следующему часу, но не совсем там). Смотрите обсуждение в комментариях. Функции времени и форматирование времени округляются, что дает неверное время, когда оно близко к доле секунды. Если вы хотите, чтобы время было точным, вам нужен вариант ответа Аганджу (если вы хотите дробные часы, вам нужно будет изменить формулу). fixer1234 6 лет назад 0

Похожие вопросы