Как использовать "кавычки" во вложенной функции IF, если Excel не видит цифры в кавычках в виде текста?

Question

Вот функция, которую я пытаюсь пристрелить.

= IFERROR (IF (ИЛИ (B40="",B41="",B42="",C40=""),"Заполните форму.",IF(D40<>"",IF(AND(D40<>"",D40< "20:00"),15, ЕСЛИ (И (Д40>="20:00", Д40<="24:59"),10, ЕСЛИ (И (Д40>="25:00 ", Д40<="29:59"), 5, ЕСЛИ (И (Д40>= "30:00", Д40<= "35:00"),-5, ЕСЛИ (D40>"35:00",-10 ""))))))), "")

Контекст: я занимаюсь разработкой оценочной таблицы для ряда учебных мероприятий. Мы используем немного теории игр и поведенческой экономики, чтобы поощрить специфическое поведение в учебных мероприятиях. Это для временной тренировки, чтобы увидеть, как быстро наши ребята могут пройти курс с малыми канатами (например, мотивационная деятельность по построению команды).

Отметки времени в "кавычках" (например, "20:00") - это количество времени, которое потребовалось нашим ребятам для прохождения курса. Это число генерируется другой ячейкой, которая принимает разницу между временем, когда наши люди начали и завершили упражнение (например, 10:45 AM - 10:36 AM).

Проблема состоит в том, что размещение кавычек вокруг промежутка времени в моей вложенной функции IF приводит к путанице в Excel. Я думаю, что это смотрит на них как текст, а не цифры. Теперь вот кикер. Если я удалю кавычки, Excel интерпретирует некоторые промежутки времени как диапазоны строк (например, 24:59). Как я могу получить вложенную функцию IF, чтобы видеть интервал времени в кавычках как метку времени, а не как текст?

Answer 1

Да, вы правы: "20:00" - это текстовая строка, а не число. Чтобы заставить ее работать так, как вы хотите, вы можете выполнить одно из следующих действий:

1. "Привести" время в кавычках, например, "20:00" +0 или "20:00"*1
2. используйте десятичный эквивалент - 20 часов 20/24
3. использовать функцию VALUE, например, VALUE("20:00")

редактировать: я предполагал, что "20:00" было 20 часов, это 20 минут? Если это так, вы можете использовать функцию TIME, как подсказывает Тони в своем комментарии..... или сделать выше, но изменить на "0:20"+0 на 20 минут..("20:00" будет интерпретироваться как Excel как 20 часы...)

Вот способ сделать то же самое, используя LOOKUP....

=IFERROR(IF(OR(B40="",B41="",B42="",C40=""),"Complete the form.",IF(D40="","",LOOKUP(D40*1440,{0,20,25,30,35;15,10,5,-5,-10}))),"")