Преобразовать текстовую строку Excel во время

27873
Charlls

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

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

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

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

0
Ну что у нас за формат ЧЧ: мм: сс.ССС? 12 лет назад 0

6 ответов на вопрос

3
Tracy Probst

Предполагая, что значение находится в ячейке 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
Robert Chudek

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

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

Поскольку я рассматриваю поздние ответы, я бы предложил использовать формат «кодовый блок» с 4 пробелами вместо стиля кавычек. Но в остальном спасибо за ответ. ^ 1 eckes 11 лет назад 0
1
Lance Roberts

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

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

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

1
z666zz666z

If you have the Time in such numeric way (900 -> 9am) or (0900 -> 9am) [without the colon]

There is a much more simple way to convert it to a Time format, just separate hour form minutes:

  • Use function INT(YourCell/100) as the Hour part
  • Use function MOD(YourCell;100) as the Minute part
  • Use value 0 as the Second part

Then use the function TIME to convert it to time type, so all combined would look like:

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

Now you only need to give it the Time format you want (1:30 PM on the list for Time).

The trick is that if you divide by one hundred and take the integer part you will loose the minute part, so you will have the "Hours", by other part if you take the module of dividing by one hundred you will get the minute part losing the hour part... then you only need to combine them.

1
Milk Man

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

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

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

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

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

0
DaveParillo

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

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

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

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

Похожие вопросы