У Сэма много рабочих листов, в которых указано время. Проблема в том, что время указывается в формате «1300», а не в формате «13:00». Таким образом, Excel видит их как обычные числовые значения, а не как время. Сэм хочет, чтобы они были преобразованы в реальные значения времени.

Есть несколько способов подойти к этой задаче. Один из способов — использовать функцию TIME для преобразования значения во время, как показано здесь:

=TIME(LEFT(A1,2),RIGHT(A1,2),)

Эта формула предполагает, что время в ячейке A1 всегда будет содержать четыре цифры. Если это не так (например, это может быть 427 вместо 0427), тогда формулу необходимо немного изменить:

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),)

Формула в основном извлекает крайнюю левую цифру (или цифры) и использует их в качестве аргумента часов функции ВРЕМЯ, а затем использует две крайние правые цифры в качестве аргумента минут. ВРЕМЯ возвращает фактическое значение времени, отформатированное как таковое в ячейке.

Аналогичный шаблонный подход можно применить с помощью функции ВРЕМЕННОЕ ЗНАЧЕНИЕ:

=TIMEVALUE(REPLACE(A1,LEN(A1)-1,0,":"))

Эта формула использует REPLACE для вставки двоеточия в нужное место, а затем TIMEVALUE преобразует результат во значение времени. Вам нужно будет отформатировать полученную ячейку так, чтобы она отображала время так, как вы хотите.

Другой вариант формульного подхода — использовать функцию ТЕКСТ следующим образом:

=--TEXT(A1,"00\:00")

Это возвращает фактическое значение времени, которое затем необходимо правильно отформатировать для отображения в виде времени.

Другой подход состоит в том, чтобы просто вычислить исходное время, чтобы преобразовать его в значение времени, используемое Excel. Это легко сделать, если вы поймете, что значения времени — это не что иное, как дробная часть дня. Таким образом, значение времени — это число от 0 до 1, полученное путем деления часов на 24 (часы в день) и минут на 1440 (минуты в день). Вот формула, которая делает это:

=INT(A1/100)/24+MOD(A1,100)/1440

Это определяет часовую часть исходного значения, которая затем делится на 24. Минутная часть (часть, оставшаяся от исходного значения) затем делится на 1440 и добавляется к первой части. Затем вы можете отформатировать результат как время, и он отлично работает.

Все формулы, описанные до сих пор, используют новый столбец для преобразования. Это удобно, но вы можете захотеть преобразовать значение на месте без необходимости в формуле. Здесь может пригодиться макрос. Следующий макрос преобразует все выбранные вами ячейки в значения времени и соответствующим образом отформатирует ячейки:

Sub NumberToTime()

Dim rCell As Range     Dim iHours As Integer     Dim iMins As Integer

For Each rCell In Selection         If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then             iHours = rCell.Value \ 100             iMins = rCell.Value Mod 100             rCell.Value = (iHours + iMins / 60) / 24             rCell.NumberFormat = "h:mm AM/PM"

End If     Next End Sub

Макрос использует целочисленное деление для определения количества часов (iHours) и помещает остаток в iMins. Затем оно преобразуется в значение времени и помещается обратно в ячейку, которая затем форматируется как время. При желании вы можете изменить формат ячейки на любой из других форматов времени, поддерживаемых Excel.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10101) применим к Microsoft Excel 2007, 2010, 2013 и 2016.

Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Converting_Numeric_Values_to_Times [Преобразование числовых значений в времена].