Функция Excel MOD не работает должным образом, если значение равно 12:00 (00.00)

Question

поэтому я разобрался с функцией MOD, чтобы вычислить свои рабочие часы, где я обычно начинал в XX PM/AM и заканчивал в XX AM/PM, где без функции MOD я не мог правильно рассчитать, если я начинал как в 5 вечера и заканчивал после полночь. Во всяком случае, это не работает должным образом, если я закончу работать до полуночи или после. Но если я закончу работать ровно в полночь и введу в ячейку значение 12 AM, в результате я получу 0, используя следующую формулу: = IF(C41 = 0,0,(MOD((C41-B41)-D41,1))* 24)Но если я укажу значение 12 и нажму клавишу ввода, это даст мне правильное количество отработанных часов, но также изменит значение на 12.01.1900 12:00:00. Это почему? Вот как выглядит таблица:

A       B           C           D       E               F
Date    Shift start Shift end   Break   Total x shift   Total
1-Oct   5:30 PM     12:00 AM            6.5             19
2-Oct   5:30 PM     12:00 AM            6.5 
4-Oct   12:00 PM     6:00 PM            6   

И формулы:

=IF(C41=0,0,(MOD((C41-B41)-D41,1))*24)

в каждой ячейке столбца Е.

Answer 1

Я не уверен, на какой вопрос ответить. В вашем ОП есть как минимум три отдельных вопроса. Изменение значения с 12 на 01.12.1900 в 12:00:00 - это просто то, как работает Excel. Вы должны отформатировать столбец как Дата / Время, поэтому он преобразует введенное значение в формат по умолчанию, указанный в ваших настройках. В зависимости от версии Excel, которую вы используете, вы также можете отформатировать столбец как просто время или изменить формат, чтобы не показывать дату, как сказал fixer1234.

Сложно сказать, почему MOD не работает, так как вы не объяснили его, но используете его очень странным образом, и это должно доставить вам неприятности, если вы передадите 0 (12:00) в качестве первого значение. Это означает, что вы пытаетесь разделить 0, что не может быть сделано. И тебе все равно это не нужно.

Ваша формула, похожая на указанную Тайсоном, может быть просто

=if((C1-B1)<0, C1+12-B1, C1-B1)

Обратите внимание, что вы хотите добавить 12 часов в «истинный» результат, а не 24, как сказал Тайсон. Вы захотите отформатировать столбцы B, C и D в качестве обычного формата времени, но для столбцов E и F вы, вероятно, захотите исключить обозначения am/pm.

Столбцы результатов должны оставаться в формате «Время», потому что в противном случае Excel будет выполнять числовую математику для значения даты-серии, которое Excel на самом деле хранит за кулисами, а не для математики даты, и все ваши результаты будут шаткими. Например, с 12:00 до 6:30 - это 0,27, если столбец результатов отформатирован как числовое поле. Измените его на формат времени, и он правильно отобразит 6:30.