У Сэма много рабочих листов, в которых указано время. Проблема в том, что время указывается в формате «1300», а не в формате «13:00». Таким образом, Excel видит их как обычные числовые значения, а не как время. Сэм хочет, чтобы они были преобразованы в фактические значения времени.
Есть несколько способов подойти к этой задаче. Один из способов – использовать функцию TIME для преобразования значения во время, как показано здесь:
= TIME (LEFT (A1,2), RIGHT (A1,2),)
В этой формуле предполагается, что время в ячейке A1 всегда будет содержать четыре цифры. Если это не так (например, это может быть 427 вместо 0427), тогда формулу необходимо немного изменить:
= TIME (LEFT (A1, LEN (A1) -2), ВПРАВО (A1,2),)
Формула в основном извлекает самую левую цифру (или цифры) и использует их для аргумента часов функции ВРЕМЯ, а затем использует две крайние правые цифры для минут аргумент. ВРЕМЯ возвращает фактическое значение времени, отформатированное как таковое в ячейке.
Аналогичный формульный подход можно применить с помощью функции ВРЕМЯЗНАЧ:
= ВРЕМЯЗНАЧ (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 Тогда iHours = rCell.Value 100 iMins = rCell.Value Mod 100 rCell.Value = (iHours + iMins/60)/24 rCell. NumberFormat = «h: mm AM/PM» End If NextEnd Sub
Макрос использует целочисленное деление для определения количества часов (iHours) и вставляет остаток в iMins. Затем оно преобразуется в значение времени и помещается обратно в ячейку, которая затем форматируется как время. При желании вы можете изменить формат ячейки на любой из других форматов времени, поддерживаемых Excel.
Источник: