Склоняем правильно время в MS EXCEL - 1 сутки 2 часа; 3 суток 6 часов

Учимся корректно склонять сутки и часы: 1 сутки 2 часа; 3 суток 6 часов.

Предположим, что в ячейке B3 введено количество часов, например 29 (простым числом, т.е. не в формате даты)

Для правильного написания суток/ сутки и час/ часа/ часов необходимо написать формулу:

=ЦЕЛОЕ(B3/24)&" сут"&ЕСЛИ(ПРАВСИМВ(ЦЕЛОЕ(B3/24);2)="11";"ок ";ТЕКСТ(ПРАВСИМВ(ЦЕЛОЕ(B3/24);1);"[=1]ки ;ок "))&
B3-ЦЕЛОЕ(B3/24)*24&" час"&ВЫБОР(B3-ЦЕЛОЕ(B3/24)*24+1;"ов";"";"а";"а";"а";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"";"а";"а")

Для числа 29 (29 часов) формула вернет 1 сутки 5 часов.

Разберем подробнее (см. Файл примера):

  • чтобы выделить количество полных суток используем выражение ЦЕЛОЕ(B3/24)
  • сначала просклоняем сутки: 0 суток, 2 суток, 3 суток, 11 суток, 20 суток, 111 суток, ..., но 1 сутки, 21 сутки, 31 сутки, ... В этом нам поможет формула 
    " сут"&ЕСЛИ(ПРАВСИМВ(ЦЕЛОЕ(B3/24);2)="11";"ок ";ТЕКСТ(ПРАВСИМВ(ЦЕЛОЕ(B3/24);1);"[=1]ки ;ок ")) 
    Здесь использован пользовательский формат в функции ТЕКСТ()
  • теперь просклоняем часы: 1 час, 2 часа, 5 часов
  • сначала выделим количество часов с помощью формулы B3-ЦЕЛОЕ(B3/24)*24
  • так как часов может быть от 0 до 23, то можно их все перебрать с помощью выражения 
    ВЫБОР(B3-ЦЕЛОЕ(B3/24)*24+1;"ов";"";"а";"а";"а";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"ов";"";"а";"а")
    ,которое просто подставляет нужное значение.

Формула вернет текстовое значение. Если требуется, чтобы в ячейке содержалось число, а отображалось, например, 5 суток 7 часов, то применять пользовательский формат через формат ячеек. Для этого в другой ячейке введите формулу =В3/24, чтобы количество часов перевести в формат понятный EXCEL (см. статью Как EXCEL хранит дату и время) Через Формат ячейки установите пользовательский формат 

[=0]Д \суток ч \ча\сов;[=1]Д \сутки ч \ча\сов;Д \суток ч \ча\сов

Этот формат позволяет склонять только сутки (из-за ограничения формата ячейки 21, 31, 41… сутки склоняются некорректно (21 суток)). Специальные знаки слеша \ использованы, чтобы EXCEL воспринял специальные символы (ч, с (часы и секунды)) как текст.

СОВЕТ:
Склонять правильно количество месяцев и дней поможет статья Склоняем правильно количество месяцев и дней.

Примечание:
Благодарим нашего пользователя grek322 за указание на ошибку при решении этой задачи (теперь статья и файл примера откорректированы).

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)

Комментарии

grek322

Формула работает при "склонении" до 20-ти часов, при количестве часов 525 получается 21 суток..часов. Спасибо за интересную статью, применил вашу формулу в своём рабочем графике для подсчёта отгулов.

Creator

Спасибо, исправлено!

Михаил Ч. (MCH) (не проверено)

Вариант (чуть короче):
=ОТБР(B3/24)&" сут"&ЕСЛИ(ОСТАТ(МАКС(ОСТАТ(ОТБР(B3/24)-11;100);1);10);"ок ";"ки ")&ОСТАТ(B3;24)&" час"&ВПР(ОСТАТ(ОСТАТ(B3;24)-1;20);{0;"":1;"а":4;"ов"};2)