У меня есть два текстовых поля с датами. Я хочу преобразовать их в даты Excel и вычесть одно из другого, например:
cell 1: Wed 18 Feb 2015 18:00
Cell 2: Mon 16 Feb 2015 07:00
Предоставление 59 часов (59:00:00 будет делать)
Я считаю, что это правильно, но я получаю #value! результат, который я предполагаю, потому что B5 не имеет правильного значения даты и времени Excel.
пожалуйста, порекомендуйте
=DATEVALUE(MID(B5,5,2) & MID(B5,8,3) & RIGHT(B5,4)) + TIME(MID(B5,17,2),MID(B5,20,2))
Вы можете конвертировать ваши значения довольно легко, например, с помощью этой формулы в D5
=MID(B5,5,20)+0
отформатируйте как требуется, например, пользовательский формат как ddd d mmm yyyy hh:mm и они будут выглядеть как прежде, но на этот раз они будут "истинными" датой / временем, насколько это касается Excel, так что вы можете просто вычесть одно из другого чтобы получить разницу, т.е. с преобразованными значениями в D5 и E5
=E5-D5
отформатируйте ячейку результата как [h]:mm:ss чтобы получить 59:00:00
или сделать преобразование и вычитание в одной ячейке с этой формулой
=MID(C5,5,20)-MID(B5,5,20)
форматировать ячейку результата как [h]:mm:ss как и раньше
см. примеры этого последнего подхода здесь
Пара предметов. Я предполагаю, что значения, указанные в качестве дат, в Excel в виде строк. Если у вас есть их как даты, это будет намного проще.
Есть две проблемы с вашими функциями DATEVALUE . Сначала извлекаемые значения неверны. Это значения из первой строки:
MID(B5,5,2) = 18
MID(B5,8,3) = Feb
RIGHT(B5,4) = 8:00
MID(B5,17,2) = 18
MID(B5,20,2) = 00
Во-вторых, DATEVALUE ожидает разбора строки. Так что вам нужны разделители. Например:
=DATEVALUE(MID(B8,5,2) & "-" & MID(B8,8,3) & "-" & MID(B8,12,4))
ПРИМЕЧАНИЕ. Этот подход хрупок, поскольку предполагает формат вашей временной шкалы. Он сломается, если какое-либо из значений не будет совпадать.
Как только вы получите правильные даты Excel, вы можете использовать следующую формулу для расчета промежутка времени в часах между двумя
=INT(B5-B6)*24 + INT(MOD(B5-B6,1)*24) &" hours"
Это даст 59 hours для вашей даты.
Вы на правильном пути с вашей формулой, но это немного сложнее, чем это. Для этого типа сценария я обычно разбиваю его на несколько вспомогательных ячеек, чтобы уменьшить сложность, хотя вы можете объединить все, если хотите. Предполагая ваше значение даты / времени в A1:
Cell Formula Result
A1 Wed 18 Feb 2015 18:00
B1 =MID(A1,8,3)&" "&MID(A1,5,2)&", "&MID(A1,12,4) Feb 18, 2015
C1 =RIGHT(A1,5) 18:00
D1 =TIMEVALUE(C1) 1/0/00 18:00
E1 =DATEVALUE(B1)+D1 2/18/2015 18:00
Затем выполните математику даты / времени на значениях на E1.
Вот комбинированная версия формулы для одной ячейки:
=DATEVALUE(MID(A1,8,3)&" "&MID(A1,5,2)&", "&MID(A1,12,4))+(TIMEVALUE(RIGHT(A1,5)))