Вычтите DateTime в этом формате

324
Never Lucky
C2 = Start date = 9/16/2018 12:41:02 B2 = End date = 9/18/2018 15:37:12 

Используя 24-часовой формат

Попытка использования =TIMEVALUE(RIGHT(C2,8))-TIMEVALUE(RIGHT(B2,8)), это не сработает, если результат должен быть больше 1 дня

Ожидаемые результаты = 2 days 02:56:10

Можно ли вычесть это в Excel?

Я хочу чтобы это было =C2-B2

Ожидаемый результат

0
Не существует формата Excel для длины периода, которая содержит часть дней. Вы можете использовать формат `[h]: mm: ss`, который даст вам результат` 50: 56: 10`. Если вы хотите создать вывод напрямую, как показано, вы должны написать свою собственную функцию форматирования. Он будет иметь тип данных 'string'. Akina 5 лет назад 1
Привет @Akina Спасибо за комментарий, я пробовал `[h]: mm: ss`, но он показывает мне` #Value! `, Я просто использую формулу` = B2-C2` Never Lucky 5 лет назад 0
Является ли ваш источник данных датой или строкой? Какое значение отображается, если не указан формат ячейки? Akina 5 лет назад 0
@ Акина, я скачал файл с какой-то вечеринки. Я думаю, что это отображается в виде строки, а не datetime, потому что формат `General` Я отредактировал описания, изображение включено Never Lucky 5 лет назад 0
Если это так, вы должны преобразовать данные строкового типа в тип datetime, а затем вычислить. Akina 5 лет назад 0
@Akina У меня есть файл предыдущей версии, который имеет формат `dd / mm / yyyy чч: мм: сс`` General`, и я могу вычесть время для 2 ячеек, и показывает ожидаемый результат, а новые версии - `mm / дд / гггг чч: мм: сс` формат `General` и я не могу вычесть Never Lucky 5 лет назад 0
* новые версии имеют формат мм / дд / гггг чч: мм: сс Общие и я не могу вычесть * Преобразуйте его в предыдущий формат с помощью обычных строковых функций. Это позволяет Excel автоматически конвертировать данные из строки в дату и время, чтобы вы могли снова выполнять вычисления. Akina 5 лет назад 0
@Akina Я попытался преобразовать его в дату / время, используя `= TEXT (B2," MM / DD / YYYY чч: мм: сс ")`, и попробуйте использовать `= E2-D2`, он все еще показывает мне` # значение! ` Never Lucky 5 лет назад 0
Что за файл? Если это текстовый или CSV-файл, вы должны ** импортировать ** файл и указать столбец даты в импорте как имеющий формат, существующий в CSV / текстовом файле. Затем Excel преобразует значение в «обычную» дату Excel, и вы сможете работать с ней. Ron Rosenfeld 5 лет назад 0

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

1
Rajesh S

=INT(C2-C1)&" days " & TEXT(C2-C1, "h"" hrs ""m"" mints """) 

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

  • Функция TEXT в этой формуле применяет пользовательский формат чисел для Hours and Minutesзначения, созданного путем вычитания End Date из Start Date.
  • Функция INT, удаляет целую часть End Date, .minusStart Date

если у вас много значений, End & Start Dates то эту формулу можно использовать.

=INT(SUMPRODUCT(C5:C9-B5:B9))&" days "&TEXT(SUMPRODUCT(C5:C9-B5:B9),"h"" hrs ""m"" mins """) 

При необходимости измените ссылки на ячейки в формуле.

Привет, я думаю, что значение в поле, которое вы использовали, отличается от моего, для получения дополнительной информации, у меня есть данные 10k, которые имеют формат, который я написал в описаниях выше, дата `9/16 / 2018`, но не` 9/16 / 18`, и в формате «24 часа» для времени. Never Lucky 5 лет назад 0
@NeverLucky, я также использовал формат `24 Hrs Date Time` для тех значений, которые я использовал в примере, причина которого` 15: 37` отражает в моем примере `3:37 PM`. Если у вас есть диапазон дат, используйте второй пример SUMPRODUCT. Обе формулы, которые я показал, работают со 100% точностью, поскольку они были протестированы. Rajesh S 5 лет назад 0
@NeverLucky, форматы ячеек: «MM / DD / YY HH: MM AM / PM», поэтому показывает «09/16/18», в противном случае фактическая дата - «09/06/2018», вы можете изменить ее на `ДД / ММ / ГГГГ ЧЧ: ММ AM / PM` !! ☺ Поскольку моя формула работает должным образом, наши результаты схожи. ! ☺ Rajesh S 5 лет назад 0
Хм, к сожалению, он все еще не работает для моего файла, чувствую себя так странно, предположим, что он должен работать с нормальным `= B2-C2`, это ломает мне голову, кстати, спасибо ~ Never Lucky 5 лет назад 0
@NeverLucky, мне очень жаль, что Вам не удалось воспроизвести его. формула находится в ячейке `C4` и снимок экрана является частью моей таблицы. Просто сделай это снова! Напишите, что формула возвращает любую ошибку или неправильный ответ ?? Rajesh S 5 лет назад 0
это возвращает # значение! я проверил, изменив дату на дд / мм / гггг, тогда я могу использовать нормальные вычитания `= C2-B2`` = TIMEVALUE (RIGHT (C2,8)) - TIMEVALUE (RIGHT (B2,8)) `я использовал эта формула раньше, и она может возвращать правильное значение, но только для даты / времени, которые находятся в пределах 1 дня, возможно ли изменить это и сделать возможным вычисление даже более чем 1 дня? Never Lucky 5 лет назад 0
@NeverLucky, найдите мой почтовый идентификатор в профиле и просто отправьте файл по почте, я проверю и исправлю его ☺ Rajesh S 5 лет назад 0
Спасибо, я написал вам по электронной почте, тогда мы пообщаемся по электронной почте, и если найдутся какие-либо решения, мы разместим его здесь ~ Cheers Never Lucky 5 лет назад 0
@NeverLucky ,, скоро я верну файл по почте, дай мне немного времени. ☺ Rajesh S 5 лет назад 0
0
Never Lucky

Так что у меня есть вид на некоторую помощь и возможность получить ответ, он не может вычитать как формат, который я экспортируемые из не распознается первенствует Так я использовал эту формулу, чтобы преобразовать все ячейки в дата / время с DD/MM/YYYY HH:MM:SSотMM/DD/YYYY HH:MM:SS

=IF(ISERR(VALUE(MID(B2,3,2))),IF(ISERR(VALUE(MID(B2,3,1))),IF(ISERR(VALUE(MID(B2,4,2))),VALUE(MID(B2,4,1)),VALUE(MID(B2,4,2))),VALUE(MID(B2,3,1))),VALUE(MID(B2,3,2)))&"/"&IF(ISERR(VALUE(LEFT(B2,2))),VALUE(LEFT(B2,1)),VALUE(LEFT(B2,2)))&"/"&MID(B2,LEN(B2)-12,4)&" "&RIGHT(B2,8)

и может использовать вычитания после этого ~

Хотелось бы получить более простой ответ для этого.

Спасибо за тех, кто предоставил решения для моих вопросов.