Формула Excel для автоматического вычитания даты / времени

Question

Я хочу сделать следующее в ячейке K14: Если ячейка D14 содержит C17 , возьмите ячейку F14 и вычтите 3 часа и 45 минут.

Формат даты / времени, который я использую, - это юлианская дата / военное время, поэтому 16271/1845 минус 3 часа 45 минут будет 16271/1500 . Эти значения даты / времени хранятся в виде текстовой строки. Юлианская дата - это двузначный год, за которым следует трехзначный день года.

Answer 1

Вот решение для вас, основанное на проблеме, как было определено изначально:

Ячейка A1 содержит образец даты / времени в вашем формате.

B1 переводит это в дату / время Excel с этой формулой:

=DATE(LEFT(A1,2),1,1)+MID(A1,3,3)-1+MID($A1,7,2)/24+RIGHT($A1,2)/1440

При этом используются текстовые функции для разбора фрагментов и построения даты за год с использованием 1 января, затем добавляется число дней (и вычитается 1, поскольку год не может начинаться с 0 января). Поскольку Excel хранит время в виде доли дня, значение часа делится на 24, а значение минуты делится на 1440 минут в день.

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

C1 содержит дату / время после вычитания 3 часов 45 минут:

=B1-TIME(3,45,0)

D1 переводит результат обратно в ваш формат:

=TEXT(C1,"yy")&TEXT(INT(C1)-DATE(YEAR(C1),1,0),"00#")&"/"&TEXT(MOD(C1,1),"hhmm")

Он объединяет части, используя & для построения строки.

• Трехзначный день - это дата результата за вычетом "января 0" года результата в одно и то же время дня (время является частью сохраненного значения, и округление может повлиять на количество дней). Функция INT извлекает дату результата в полночь, чтобы соответствовать времени вычисленной даты января 0.
• Функция MOD обеспечивает дробную часть результата, которая является частью времени, и использует функцию TEXT для построения отображения часов и минут в 24-часовом формате.

Вы можете сделать все это в одной, сложной для обслуживания ячейке, заменив формулы для ссылок на ячейки. В D1 замените каждую ссылку C1 на формулу в C1 после начального = . Затем замените каждую ссылку B1 формулой в B1 после начального = .

Ваш вопрос описывает условие ЕСЛИ, но вы не предоставили достаточно информации для него. Замените F14 вместо A1 в вашей формуле, чтобы указать правильную ячейку ввода. IF в ячейке K14 будет выглядеть так:

=IF(D14="C17",formula based on above answer,"")

Не дублируйте начальные = из моей формулы ответа, когда вы вставляете формулу из ответа в IF. Я предположил ноль (пустые кавычки, которые дадут вам пустую ячейку) для ложного условия, но вы можете сделать это как угодно.