Блог

Это чисто вопрос листа Excel.

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

Мне удалось получить разницу во времени в количестве часов и минут, изменив формат чисел на [h]:mm . Теперь я хотел бы получить разницу в количестве дней, часов и минут. Я попытался установить числовой формат [d]:[h]:[mm] , но он не принимается Excel.

Вот что у меня есть сейчас:

     A                   B                   C (=A2-A1)
    ----------------    ----------------    ----------
1|  14/10/2011 00:00    17/10/2011 07:50    79:50
2|  14/10/2011 00:00    17/10/2011 11:00    83:00
  

Вот что я хотел бы получить:

     A                   B                   C (=A2-A1)
    ----------------    ----------------    ----------
1|  14/10/2011 00:00    17/10/2011 07:50    3:7:50
2|  14/10/2011 00:00    17/10/2011 11:00    3:11:00
  

Как я могу это сделать? Доступен ли для этого числовой формат?

Вы можете использовать TEXT
= TEXT(B1-A1,"d:h:mm")

Обратите внимание, что того же эффекта можно добиться, используя простой числовой формат непосредственно для ячеек

• выберите свой диапазон (ячейки C1, C2 и т. Д.)
• щелкните правой кнопкой мыши и отформатируйте ячейки
• Пользовательский
• Тип d:hh:mm

Если, в отличие от данных вашего примера, разница в датах превышает 31 день, то такой подход, как =INT(B1-A1)amp;":"amp;TEXT(B1-A1,"h:mm")
будет работать

Предупреждение: приведенное выше работает только для диапазонов менее 31 дня. используйте

 =CONCATENATE(TEXT(FLOOR(B1-A1,1),"@")," Days",TEXT(B1-A1," h:mm:ss"))
  

вместо этого для диапазонов выше 31 дня. Это приведет к неправильной сортировке, поэтому было бы лучше выполнить вычисления в одном столбце, а затем изменить этот столбец для конечного результата.

К сожалению, оказывается, что форматы number и datetime не могут быть объединены, в противном случае пользовательский формат:

 0:h:m
  

это был бы билет. Однако, если для интервалов в 32 дня или более вас устраивает просто отображение количества (дробных) дней, вы можете использовать пользовательский формат, например:

 [>=32] 0.00 "days"; d:h:m
  

Значение ячейки остается числовым.

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

# дни ## часы ## минуты ## секунды

 =CONCATENATE(TEXT(FLOOR(B2-A2,1),"@")," days ", IF(HOUR(A12)-HOUR(A11)<0,HOUR(A12)-HOUR(A11) 24,HOUR(A12)-HOUR(A11))," hours ",IF(MINUTE(A12)-MINUTE(A11)<0,MINUTE(A12)-MINUTE(A11) 60,MINUTE(A12)-MINUTE(A11)), " minutes ", TEXT(B2-A2,"s"), " seconds")
  

Следующий подход также работает, предполагая, что ваша дата начала указана в ячейке C2, а дата окончания — в ячейке D2:

  =TEXT((D2-C2)-MOD(D2-C2,1),"0") amp; " days " amp; TEXT(MOD(D2-C2,1),"hh:mm")
  

=IFERROR(INT(Date1-Date2)amp;" Days "amp;INT(MOD((Date1-Date2);INT(Datw1-Date2))*24)amp;" Hours "amp;MINUTE(Fate1-Date)amp;" Min";IFERROR((Date1-Date2);"NA"))enter code here

Я думаю, это должно сработать, какая бы дата у вас ни была.

У меня был подобный вопрос, кроме меня, мне нужны были часы, разделенные на 8, а не на 24, поскольку мне нужны были рабочие дни. Итак, я придумал решение, но оно действительно очень сложное для понимания, потому что код выглядит как беспорядок, но если вы сегментируете их, это не так сложно.

 =CONCAT(INT(C16*24/8),":",IF(INT((C16-INT(C16))*24)<10,CONCAT("0",INT((C16-INT(C16))*24)),INT((C16-INT(C16))*24)),":",IF(MINUTE(C16)<10,CONCAT("0",MINUTE(C16)),MINUTE(C16)))
  

• Обратите внимание, что я использовал «8«, чтобы разделить часы на 8, потому что мне нужно было рабочее время. Таким образом, вы можете пренебречь этим, если вы этого не хотите
• Мне нужно было отобразить результат как «4: 09: 08«, а не «4: 9: 8«. (Это в формате «Дни: часы: минуты»). Вот почему я использовал дополнительные функции IF() и CONCAT() внутри.