Как преобразовать временное текстовое значение в правильное время

Question

Моя таблица имеет значения времени, как показано:
:15
:00:23
:47:15

Excel не видит их как фактические значения времени, а вместо этого возвращает их значения в виде текста. Поэтому математические функции не будут работать, потому что Excel не видит их как числа.
Как я могу преобразовать эти значения в правильные числа, и это может быть сделано без использования VBA?

Answer 1

Правильно построенный, вложенный набор операторов IF должен обрабатывать это:

Вот формула:

=IF(LEFT(C7,1)=":",IF(ISERROR(FIND(":",C7,2)),TIMEVALUE("0:0"&C7),TIMEVALUE("0"&C7)),C7)

Как это устроено:

• Внешнее условие IF(LEFT(C7,1)=":",...) использует тот факт, что все искаженные значения приводятся с двоеточием.

  • Если первый символ не является двоеточием, то значение будет хорошим, если сообщить о нем как есть: C7
  • Если первый символ является двоеточием, то управление передается внутреннему условному выражению.
• Как уже отмечалось, внутреннее условное IF(ISERROR(FIND(":",C7,2),...) активируется только в том случае, если двоеточие фактически найдено в качестве первого символа. Затем он пытается найти двоеточие в оставшейся части строки. В соответствии с поведением функции FIND , если двоеточие не найдено, #VALUE! ошибка возвращается и обнаруживается ISERROR
• Если FIND возвращает ошибку, к строке должны быть добавлены как цифры нулевого часа, так и цифры нулевой минуты: "0:0"&C7
• Если FIND возвращает число, то присутствует другое двоеточие, и перед ним должен стоять только номер нулевого часа: "0"&C7
• TIMEVALUE используется во всех случаях, чтобы гарантировать, что строка значения времени фактически интерпретируется как значение времени.