excel - время округления до ближайших 15 минут

У меня есть формула, которая вычисляет разницу в несколько TIMES и умножает ее на 24, чтобы получить десятичное значение для суммы:

D10=(C9-D9)*24

Я хотел бы сделать специальное округление следующим образом:

если D9 is 1pm и C9 is 2:08 pm, я бы хотел, чтобы D10 был 1.25

другие слова

1:53 until 2:07 would be counted as 2
2:08 until 2:22 would be 2:15
2:23 through 2:37 would be 2:30
etc

Кто-нибудь знает, как сделать такое специальное округление?

как выразился Дуг:

i need the accrued time from 1 PM to 2:08 PM should round up to 1.25 hours whereas 1 PM to 2 PM would round down to 1 hour, in other words to the nearest quarter hour.

excel vba worksheet-function
person Alex Gordon    schedule 31.05.2011    source источник
comment
Ваша математика не работает. 13:00 + 14:08 = 3:08. 13:00 + 14:15 = 3:15. Если вычесть 1, а затем умножить десятичную часть на 24, получится 3,25.   -  person Lance Roberts    schedule 01.06.2011
comment
@ Лэнс, ОП говорит, что накопленное время с 13:00 до 14:08 должно округляться до 1,25 часа, тогда как с 13:00 до 14:00 округляется до 1 часа, другими словами, до ближайшей четверти часа.   -  person Doug Glancy    schedule 01.06.2011
comment
@lance да, даг прав   -  person Alex Gordon    schedule 01.06.2011
comment
@Doug, значит, ему нужна разница, а не сумма?   -  person Lance Roberts    schedule 01.06.2011
comment
@Лэнс, да! разница. извините, я обновлю прямо сейчас   -  person Alex Gordon    schedule 01.06.2011

Ответы (4)


arrow_upward
4
arrow_downward

Вот формула, которая разделит разницу, как вы хотели:

=(TRUNC((D9+VALUE("00:07"))*96)-TRUNC((C9+VALUE("00:07"))*96))*VALUE("00:15")*24
person Lance Roberts    schedule 31.05.2011
comment
@I__, ну тогда я могу это исправить, но это значит, что ты просишь не сумму, ты просишь разницу. Я внесу изменения сегодня днем. - person Lance Roberts; 01.06.2011
comment
@lance Эй, Ланс, у тебя есть шанс это исправить? - person Alex Gordon; 01.06.2011
comment
@I__, хорошо, готово, но имейте в виду, что есть небольшие ошибки, которые иногда влияют на TRUNC, поэтому он не всегда может работать идеально. Я обнаружил, что лучше всего это работает с операторами VALUE, поэтому не пытайтесь изменить их на их десятичные эквиваленты. - person Lance Roberts; 01.06.2011
comment
@lance не работает, для этих значений: 13:08 14:03 я получаю результат 1 - person Alex Gordon; 01.06.2011
comment
@I__, как и должно быть, поскольку разница составляет 0:55, а это в пределах 7 минут от 1 часа, как вы указали. - person Lance Roberts; 01.06.2011
comment
@lance это должно перевести как 2:00 - 1:15 = 0:45 - person Alex Gordon; 01.06.2011
comment
@I__, хорошо, попробуй еще раз. Самое сложное в программировании — это именно понимание требований пользователя. - person Lance Roberts; 01.06.2011
comment
@I__, запутался, поменяй второй D9 на C9 - person Lance Roberts; 02.06.2011

arrow_upward
4
arrow_downward

Если вам нужна разница во времени в часах, но округленная до ближайшей четверти часа, то, возможно, все, что вам нужно, это что-то вроде:

=MROUND((C9-D9)*24,0.25)
person Brian Camire    schedule 22.06.2011

arrow_upward
2
arrow_downward

Версия функции Excel:

Согласно запросу в комментариях, здесь версия только для функции excel:

ОКРУГЛЕНИЕ ДО СЛЕДУЮЩИХ 15 МИНУТ:

=ВРЕМЯ(ЧАС(A1),15*ПОТОЛОК(МИНУТЫ(A1)/15,1),0)

  • Н.Б. замените A1 на (t2-t1) в соответствии с исходным вопросом

ОКРУГЛЕНИЕ ДО БЛИЖАЙШЕЙ 15-МИНУТНОЙ ГРАНИЦЫ:

=ВРЕМЯ(ЧАС(A1),15*ОКРУГЛ(МИНУТЫ(A1)/15,0),0)

Кстати: следует избегать преобразования в строку для манипулирования датами как по причинам производительности, так и, возможно, также по гео-причинам. Возможно, ни одна из этих вещей не беспокоит вас.

Версия VBA

вы можете сделать это с помощью функций div и mod. Не уверен, хотите ли вы это в макросах vba или excel, но вот подход. Передо мной сейчас нет Excel, так что вот...

(сделайте в vba, если вам нужно много раз использовать это)

преобразовать в дробь

д = пол (n / 0,25)

остаток=n по модулю 0,25

х=0,25 * раунд (остаток / 0,25)

ответ = (д * 0,25) + х

это должно быть близко.

... только что подумал о привязке excel к десятичной функции (что это было еще раз). Это может быть лучший путь.

закодировал его в vba... только базовое тестирование:

Public Function f(n As Double) As Double

    Dim d As Double, r As Double, x As Double

    d = Int(n / 0.25)
    r = n - (d * 0.25)   ' strange... couldn't use mod function with decimal?
    x = 0.25 * Round(r / 0.25)

    f = (d * 0.25) + x

End Function

ПРИМЕНЕНИЕ:

(в клетке)

=f(c9-d9)

person sgtz    schedule 31.05.2011
comment
можете ли вы показать мне чистый способ excel без vba сделать это большое спасибо - person Alex Gordon; 01.06.2011

arrow_upward
1
arrow_downward

Вот еще формальный вариант

=(ROUND(+D9*96,0)-ROUND(+C9*96,0))/4

Преобразует каждый временной ряд в 1/4 часа (*96), округляет, добавляет результаты и масштабирует до часов (*4).

person chris neilsen    schedule 01.06.2011
comment
Опечатка в формуле должна была быть - не +, исправлен ответ сейчас - person chris neilsen; 01.06.2011