Пользовательский формат Excel: дата / время записаны?

Question

Я пытаюсь отформатировать дату и время в Excel 2010, чтобы они выглядели примерно так:

1.234 ==> 1 день, 5 часов, 36 минут

Я знаю, как сделать что-то подобное для более простых сценариев

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

[=1] d" day";d" days"

Но это не работает с отметкой времени.

Для всего набора я могу сделать:

d" days, "h" hours, "m" minutes"

но это выглядит забавно, когда одно из этих значений (часто дней) равно 1 .

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

1 день, 5 минут

если значение часов равно 0, но

1 день, 0 часов, 5 минут

работает так же хорошо.

Я могу создать решение с помощью VBA/UDF, но на данный момент в рабочей книге нет другого кода, поэтому я бы хотел, если возможно, избежать этого пути. Я также знаю, что могу разобрать значение в другой ячейке следующим образом:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & ", " & etc...

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

Это действительно возможно с условным числовым форматом, или я застрял с более длинным, вытянутым методом?

Answer 1

Вы не можете сделать это с пользовательским числовым форматом, это слишком сложно для простых условных выражений, таких как [= 1], и вы не можете использовать какие-либо функции в поле Тип.

Вам нужна формула в отдельной ячейке. (Полагаю, вы могли бы скрыть исходную колонку.)

Answer 2

Судя по отзывам, это кажется слишком большим для простого формата. Вместо этого я разработал несколько формул для создания этого.

Чтобы отобразить все значения, включая все 0 значений:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & ", " & MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & ", " & SECOND(A1) & IF(SECOND(A1)=1," second"," seconds")

1.25001 : 1 day, 6 hours, 0 minutes, 1 second
2.25001 : 2 days, 6 hours, 0 minutes, 1 second
1.04167 : 1 day, 1 hour, 0 minutes, 0 seconds

Чтобы удалить только конечные значения 0:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & IF(HOUR(A1) + MINUTE(A1) + SECOND(A1)>0, ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & IF( MINUTE(A1) + SECOND(A1)>0, ", " & MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & IF( SECOND(A1)>0, ", " & SECOND(A1) & IF(SECOND(A1)=1," second"," seconds"), ""), ""), "")

1.25001 : 1 day, 6 hours, 0 minutes, 1 second
2.25001 : 2 days, 6 hours, 0 minutes, 1 second
1.04167 : 1 day, 1 hour

Если все значения 0 должны быть опущены, это будет работать:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & IF(HOUR(A1) + MINUTE(A1) + SECOND(A1)>0, ", ", "") & IF(HOUR(A1)>0,HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & IF( MINUTE(A1) + SECOND(A1)>0, ", ", ""),"") & IF(MINUTE(A1)>0, MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & IF( SECOND(A1)>0, ", ", ""),"") & IF(SECOND(A1)>0,SECOND(A1) & IF(SECOND(A1)=1," second"," seconds"),"")

1.25001 : 1 day, 6 hours, 1 second
2.25001 : 2 days, 6 hours, 1 second
1.04167 : 1 day, 1 hour