В Excel, учитывая число, представляющее некоторое количество времени (например, 25,5 часов), есть ли способ отформатировать его так, чтобы оно показывалось как 1d 1h 30m?
Щедрый вопрос:
Есть ли способ добавить месяцы к этому формату? например, 1М 1д 1ч 1м. Проблема с текущим форматом в том, что он ничего не обрабатывает в течение 32 дней (т.е. 32,5 отформатирован как 1d 8h 0m)
Вам нужно будет преобразовать эти часы во что-то, что Excel сможет понять.
Предполагая, что ваши часы представлены в десятичной форме (например, 25,5 часа, 37,25 часа), вот как вы можете это сделать:
Шаг 1:
Разделите сумму на 24.
Получает соотношение часов и дней или конвертирует сумму в доли дня (например, 25 часов конвертируется в 1 1/24 или 1,04).
Шаг 2:
Измените числовой формат ячейки, содержащей формулу или преобразованное значение, на Пользовательский.
(нажмите Ctrl + 1 > Пользовательский).
Используйте эту строку формата:
[<=0.0416551] [m]"m";[<1]h"h" m"m";d"d" h"h" m"m"
0,0416551 = 0:59:59 в десятичном виде
или же
[<1]h"h" m"m"; d"d" h"h" m"m"
или же
d"d" h"h" m"m"
Пример:
Если время в часах, то это один из способов:
=TRUNC(A1/24) & "d " & TRUNC(A1-TRUNC(A1/24)*24) & "h " &
60*((A1-TRUNC(A1/24)*24)-TRUNC((A1-TRUNC(A1/24)*24))) & "m"
(не могу добавить как комментарий, так как у меня нет 50 репутации)
Чтобы отображались месяцы, вы можете использовать следующий формат (обратите внимание, что это не соответствует вашим точным потребностям, но вы можете изменить его соответствующим образом). Я также добавил некоторые цвета в свои строки, чтобы уточнить временные диапазоны: в верхнем примере красный - больше одного дня, черный - в течение дня. Во втором примере красный для года +, сине-зеленый для периода от четверти до года и синий для остальных. Конечно, вы можете использовать различные спецификации форматирования по своему желанию.
[Red][>1]yy\y mm\m dd\d hh:mm:ss;[Black][<=1]hh:mm:ss;[Red]-0.000000;[White]@ или [Red][>=364.5]yy\y mm\m dd\d;[Color50][<=122]mm\m dd\d;[Color5]mm\m dd\d;[Black]@
Чтобы отформатировать «32,5» как 32d 12:00:00, вы можете использовать [d]\d hh:mm:ss , отметьте скобки, которые указывают, что вы хотите включить значения больше 31 для количества дней.
\ Notation инструктирует Excel специально включать букву, следующую за символом \, вместо того, чтобы интерпретировать ее как метаданные для команды (например, d, без косой черты, просто немедленно выведет дни снова). Это эквивалентно нотации "", которую вы видите в других примерах.
Несколько ограничений и / или ошибок:
1) вы не можете указать более 4 типов форматирования, последний из которых должен быть только для текста. Это означает, что только 3 можно использовать для форматирования дат, 3-я из которых не может содержать сравнения больше или меньше, чем сравнения.
2) мм как месяцы на самом деле высока на 1 при выполнении разницы в датах (по состоянию на Excel 2010), так как Excel фактически сохраняет разницы в датах как полные даты и нет 0-го месяца, а январь является первым месяцем. Дни, по-видимому, не имеют этой проблемы, так как «1.1» отображается как 1 день, а 0.9 - как 0 дней (оба с 1 месяцем:P). Я не знаю способ сделать математику в диалоге стиля ячейки (например, (mm-1)\m), поэтому, если вы действительно хотите, чтобы эти числа были точными (вычтите 1 из месяца), вам нужно написать vB скрипт для его преобразования и отображения окончательной текстовой строки.
3) Excel не обрабатывает отрицательные разницы во времени должным образом
В качестве альтернативы, вы можете выполнить форматирование в самой ячейке с помощью формулы
=IF((B2-A2)>1,DATEDIF(A2,B2,"y")&"y "&TEXT(DATEDIF(A2,B2,"ym"),"00")&"m "&TEXT(DATEDIF(A2,B2,"md"),"00")&"d",TEXT(A2-B2,"hh:mm:ss"))
Это отобразит информацию в формате чч: мм: сс, если меньше суток, и y MM дд, если больше суток. Это не будет связано с ошибкой за один месяц, как указано выше. Однако эта ячейка на самом деле является текстовой и больше не является числовым значением. Таким образом, вы не можете (напрямую) делать дальнейшую математику с ним.