☰

Excel 2016/ Помогите создать функцию для добавления числа (48,20) в виде часов.минут к дате (2018-03-04 08:00)

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
Excel 2016/ Помогите создать функцию для добавления числа (48,20) в виде часов.минут к дате (2018-03-04 08:00)

Я бы увеличил Дату (2018-03-04 08:00) на указанное число.

Бывший.

Ячейка A1 (2018-03-04 08:00)

Ячейка A2 (48.20) ///Число в виде часов и минут, в данном случае это 2 дня и 20 минут

ячейка A3 (сложение чисел A2 с датой A1) = (2018-03-06 08:20)

решение1

Предполагая,

  • Содержимое ячейки A2 представляет собой десятичное число, представляющее часы и минуты, и оно имеет формат 00.00 (так что 48 часов, 2 минуты будут равны 48.02), тогда:
=A1+TEXT(A2*100,"00\:00")

Если вы предпочитаете более непонятные формулы, попробуйте:

=A1+DOLLARDE(A2,60)/24

решение2

Основная трудность в этом вопросе заключается в том, что значение, которое нужно прибавить, записано как десятичное число.48.20, но это следует толковать как48 часов и 20 минут. Это, по-видимому, требование. Этот ответ предполагает, что минуты закодированы как двузначные числа, поэтому 2 минуты будут .02, а не .2.

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

C2=INT(B2/24)или

C2=INT(CONVERT(B2,"hr", "day"))

Обратите внимание, что это работает только для случаев, когда часы являются кратными целым дням. Это не даст правильного ответа, если часы не являются целыми днями. Кроме того, вторая формула не будет работать, если записи фактически хранятся в виде текста.

Далее дробная часть преобразуется в минуты в ячейке D2:

=100*(B2-TRUNC(B2))или

=(B2-INT(B2))*100или

=MOD(B2,1)*100

Наконец, эти промежуточные результаты добавляются к дате с использованием этой окончательной формулы в E2:

=DATE(YEAR(A2),MONTH(A2),DAY(A2)+C2)+TIME(HOUR(A2),MINUTE(A2)+D2,SECOND(A2))

введите описание изображения здесь

Примечание: не обращайте внимания на опечатку на этом изображении. Третий столбец должен быть назван «Дни», а не «Часы».

Здесь Day(A2)+C2 добавляет 2 дня, а Minutes(A2)+D2 добавляет 20 минут.

Окончательный результат в ячейке E2 — 2018-03-06 08:20.

Примечание:

  • A2 и E2 имеют формат ГГГГ-ММ-ДД ЧЧ:ММ.
  • B2 отформатирован как число.
  • C2 и D2 отформатированы как «Общий».

Это решение использует вспомогательные столбцы для помощи в объяснении. Вспомогательные столбцы могут быть включены в одну формулу путем замены формулы вспомогательной ячейки на ссылку на ячейку.

Связанный контент