Мне нужно отформатировать некоторое прошедшее время в ячейке Excel. Ячейка должна показывать часы и минуты. Это возможно с помощью формата [h]:mm. Использование разделителя тысяч в этом формате невозможно. Наконец, вывод должен автоматически добавлять :00, если пользователь не вводит эти минуты. Я пробовал несколько форматов, но ни один из них не дал мне желаемых результатов:
input | desired | output with a custom cell format |
value | output | [h]:mm | #.###":00" |
-----------------------------------------------------------
12:34 | 12:34 | 12:34 | 1:00 |
123:45 | 123:45 | 123:45 | 5:00 |
1234:56 | 1.234:56 | 1234:56 | 51:00 |
12345:56 | 12.345:56 | 12345:56 | 12345:56 |
12 | 12:00 | 288:00 | 12:00 |
123 | 123:00 | 2952:00 | 123:00 |
1234 | 1.234:00 | 29616:00 | 1.234:00 |
12345 | 12.345:00 | 296280:00 | 12.345:00 |
Редактировать: исходная таблица вводила в заблуждение. Переформатировано, чтобы лучше показать проблему.
Никаких расчетов производить не нужно.
Может ли кто-нибудь дать мне подсказку?
Большое спасибо.
решение1
Если вы вводите данные, 12345:56то это будет текстовое значение по определению (это просто недопустимое числовое значение), и вам понадобится какой-то перевод/вычисление для создания пользовательского формата.
=IF(colon in string is true, the format left substring as ..... etc. etc.
Я вижу, что это работает только так
Опция 1
- В зависимости от способа ввода данных (включая двоеточие),
- Чтобы отформатировать входные ячейки,
- Не требуя дальнейших расчетов,
по вашему запросу, перейти к вашим настройкам:
- Файл > Параметры > Дополнительно > Изменить десятичный разделитель на
: - Отформатируйте диапазон чисел с двумя десятичными знаками и нажмите кнопку «Использовать разделитель тысяч».
Теперь любое введенное вами число будет использовать .как разделитель тысяч, так и разделитель :перед десятичными знаками без каких-либо вычислений (согласно вашему вопросу)
Я также предположил, что ваш желаемый вывод для предпоследнего значения должен содержать разделитель тысяч?
Вариант 2
Другой вариант — использовать другую соседнюю ячейку для вывода ожидаемого результата. Таким образом, вы можете оставить десятичный разделитель как есть, но:
- Отформатируйте входной столбец как текст
Введите следующую формулу в соседний столбец (см. пример, предполагая, что вашим десятичным разделителем будет запятая):
=SUBSTITUTE(TEXT(SUBSTITUTE(A2,":",","),"#.###,00"),",",":")
