1

Есть столбец с периодами времени в формате hh:mm:ss . Секунды не используются. Период времени в три часа и две минуты будет 03:02:00 . Как условно отформатировать ячейки, чтобы получить следующие результаты?

00:00:000 minutes

00:01:001 minute

00:02:002 minutes

01:00:001 hour

02:00:002 hours

03:01:003 hours, 1 minute

01:03:001 hour, 3 minutes

Я неудачно попробовал строки нестандартного формата, такие как:

[<60]hh:mm:ss "minutes";[<120]hh:mm:ss "minute";[<3600]hh:mm:ss "minutes";hh "hour(s)," mm "minute(s)"

3 ответа3

1

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

Однако причина, по которой он не работает для вас, заключается в том, что значения времени не выражаются в секундах, как вы предполагаете, - они хранятся в десятичном значении, где 1 - 24 часа.

Таким образом, вы должны использовать десятичные значения, эквивалентные желаемым точкам останова. К сожалению, кажется, что Excel ограничивает количество точек останова тремя, поэтому я не могу получить дополнительные точки останова для "1 минуты" (единственного числа) и т.д. Даже если бы было больше точек останова, вы никогда не могли бы достичь "N часов", потому что вы бы нужны бесконечные точки останова (нет условия, которое обнаруживает компонент с нулевой минутой).

Итак, самое близкое, что я могу получить с пользовательским форматом:

[<0.000694]"0 minutes";[<0.041666][m] "minutes";h "hours", m "minutes"

Что дает следующее:

00:00:00    0 minutes
00:01:00    1 minutes
00:02:00    2 minutes
00:03:00    3 minutes
01:00:00    1 hours, 0 minutes
01:01:00    1 hours, 1 minutes
02:00:00    2 hours, 0 minutes
02:01:00    2 hours, 1 minutes
01:03:00    1 hours, 3 minutes
23:59:59    23 hours, 59 minutes

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

Но также я бы посоветовал вам подумать, должно ли значение быть временем в первую очередь. Хранение истекшего периода будет лучше в определенной единице (например, в вашем случае, в минутах). У вас по-прежнему не может быть бесконечных точек останова, но вы избежите очевидной проблемы с вышеперечисленным - она переносится через 23 часа 59 минут.

РЕДАКТИРОВАТЬ: Для удовольствия я сделал формулу, которая дает желаемые результаты. Это не тот формат, который требует ваш вопрос, но поскольку он не дал желаемого результата, я подумал, что это может быть хорошей альтернативой:

=if(hour(a1)=0,if(MINUTE(A1)=0,"0 minutes",if(minute(a1)=1,"1 minute",minute(a1) & " minutes")),if(hour(a1)=1,if(MINUTE(A1)=0,"1 hour",if(minute(a1)=1,"1 hour, 1 minute","1 hour, " & minute(a1) & " minutes")),if(MINUTE(A1)=0,hour(A1) & " hours",if(minute(a1)=1,hour(A1) & " hours, 1 minute",hour(A1) & " hours, " & minute(a1) & " minutes"))))

00:00:00    0 minutes
00:01:00    1 minute
00:02:00    2 minutes
00:03:00    3 minutes
01:00:00    1 hour
01:01:00    1 hour, 1 minute
02:00:00    2 hours
02:01:00    2 hours, 1 minute
01:03:00    1 hour, 3 minutes
23:59:59    23 hours, 59 minutes
1

Выберите столбец Time и просто примените этот формат, h" hours and "m" minutes" . Ваш столбец Дата будет выглядеть следующим образом.

1

Вы можете попробовать использовать TEXT() в формуле с множеством вложенных IF() . Это будет очень громоздким в вашем случае, потому что у вас есть так много возможных форматов. Я насчитал 8 разных возможностей.

В качестве примера эта формула

=IF(HOUR(A1)=1,IF(MINUTE(A1)=1,TEXT(A1,"h"" hour, ""m ""minute"""),TEXT(A1,"h"" hour, ""m ""minutes""")),TEXT(A1,"h"" hours, ""m ""minutes"""))

выполняет желаемое форматирование, как показано в таблице ниже.

Формат внутри функции TEXT() должен быть заключен в одинарные кавычки, но кавычки, окружающие метки часов и минут, должны быть "экранированы" с помощью двойных кавычек.

Кроме того, я не смог правильно отобразить только минуты. Эта формула дает "1 минуту" для каждого значения в столбце A:

=TEXT(A1,"m"" minute""")

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .