Я пытаюсь экспортировать из старой системы, которая хранит время в / время в виде текстовой строки в течение 24 часов.

Например, 3 часа дня, вводится как 1500 (без двоеточия)

Есть ли простой способ конвертировать эти значения в 12-часовое время с AM / PM?

У меня более 3000 записей - и я почти готов к ручному редактированию, поэтому, пожалуйста, любые предложения приветствуются.

|источник

6 ответов6

3

Предполагая, что значение находится в ячейке A1, используйте эту формулу:

=TIMEVALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))

То, что происходит, это то, что он принимает значение, равное 1 или 2 цифрам для часа и всегда 2 цифры для минуты. Например, 1500 = 15:00, 900 = 9:00. Вам может потребоваться отформатировать ячейку как время вместо числа.

Если у вас есть значения, которые раньше, чем 1:00 утра, и ваши данные не имеют ведущих нулей, вы захотите вместо этого эту более сложную формулу:

=TIMEVALUE(IF(LEN(A1)>2,LEFT(A1,LEN(A1)-2),"0")&":"&RIGHT(A1,2))

|источник
2

В Excel 2003 эти формулы работали нормально, пока не достигли времени без цифры часа (первый час после полуночи), после чего он вывел # #VALUE! ошибка. Я добавил оператор IF, чтобы иметь дело с тем первым часом, когда были минуты с одним или двумя номерами:

= ВРЕМЗНАЧ (ЕСЛИ (LEN (А1)<= 2, "0:" & ПРАВЫЙ (A1,2), (ЛЕВЫЙ (А1, LEN (A1)-2)& ":" & ПРАВЫЙ (A1,2))))

|источник
1

Ответ намного проще. У меня такая же проблема.

Используйте пользовательский формат 00\:00 в ячейке ввода.

Чтобы превратить эту ячейку в фактическое время для использования в вычислениях, используйте
=TIME(TRUNC(A1/100),MOD(A1,100),0)

У меня отлично работает ;)

источник: http://www.pcmag.com/article2/0,2817,2316755,00.asp

|источник
1

Если у вас есть Время таким числовым способом (900 -> 9:00) или (09:00 -> 9:00) [без двоеточия]

Существует гораздо более простой способ преобразовать его в формат времени, просто разделив часы на минуты:

  • Используйте функцию INT(YourCell/100) в качестве части часа
  • Используйте функцию MOD(YourCell;100) в качестве минутной части.
  • Используйте значение 0 в качестве второй части

Затем используйте функцию TIME чтобы преобразовать ее в тип времени, чтобы все вместе выглядело так:

  • =TIME(INT(YourCell/100);MOD(YourCell;100);0)

Теперь вам нужно только указать желаемый формат времени (13:30 в списке «Время»).

Хитрость в том, что если вы поделите на сотню и возьмете целую часть, вы потеряете минутную часть, поэтому у вас будут "часы", с другой стороны, если вы возьмете модуль деления на сотню, вы получите минутную часть. потеря части часа ... тогда вам нужно только объединить их.

|источник
1

Эта формула, измененная из Дейва и Трейси, выведет нужную вам текстовую строку и не потребует от вас форматирования ячейки:

=TEXT(LEFT(A1,LEN(A1)-2) &":"& RIGHT(A1,2), "h:mm AM/PM")

Если вам нужны только часы, измените значение h:mm на h.

|источник
0

Не совсем уверен, как ваши значения хранятся в ваших ячейках, т.е. они заключены в двойные кавычки? с префиксом одной кавычки? Это будет иметь значение из-за того, как обрабатывается ведущий 0.

Вы можете попробовать:

=TEXT(LEFT(A1,2) &":"& RIGHT(A1,2), "hh:mm AM/PM")

Я ожидаю, что это будет работать для большинства записей (утренние записи могут быть проблемой.

|источник

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