Формат прошедшего времени Excel
В Excel, учитывая число, представляющее некоторое количество времени (например, 25,5 часов), есть ли способ отформатировать его так, чтобы оно показывало как 1d 1h 30m?
Щедрый вопрос:
Есть ли способ добавить месяцы к этому формату? например, 1М 1д 1ч 1м. Проблема с текущим форматом в том, что он ничего не обрабатывает в течение 32 дней (т.е. 32,5 отформатирован как 1d 8h 0m)
3 ответа
Вам нужно будет преобразовать эти часы во что-то, что 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 могут использоваться для форматирования дат, третья из которых не может содержать больше, чем / меньше, чем сравнения.
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 дд, если больше суток. Это не будет связано с ошибкой за один месяц, как указано выше. Однако эта ячейка на самом деле является текстовой и больше не является числовым значением. Таким образом, вы не можете (напрямую) делать дальнейшую математику с ним.