Отображать минуты как Ч: ММ

Question

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

   A     B
---- -----
   5  0:05
  30  0:30
  60  1:00
  90  1:30
1440 24:00
1800 30:00
 -45 -0:45

Я совсем близко

1. Установите для столбца B значение =A1/(24*60) .
2. Применить [h]:mm;@ пользовательский формат.

Но отрицательные значения отображаются как ######### .

Что мне не хватает?

Answer 1

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

=IF(J8<0,"-","")&TEXT(ABS(J8)/60/24,"[H]:MM")

Отрегулируйте ссылку на ячейку в соответствии с вашими потребностями.

Answer 2

Для полноты, вот комбинация моего оригинального подхода и ответа Форварда Эда, который позволяет положительным значениям оставаться числовыми (отрицательные - все еще текст, поэтому молча отбрасываются из расчетов):

• Формула:

  =IF(A1<0, "-" &TEXT(ABS(A1)/60/24, "[H]:MM"), A1/60/24)
  

• Стиль ячейки:

  [h]:mm;@