将分钟数在8,23,38,53之间收集到最近的一刻钟

Here’s a challenge for someone. I’m trying to round session times up to the nearest quarter hour (I report my total client hours for license credentialing)

这对某人来说是个挑战。我正在尝试将会话时间缩短到最接近的四分之一小时(我报告了我的总客户端时间以获取许可证凭据)

8 minutes or above: round up to 15     
23 minutes or above: round up to 30        
38 minutes or above: round up to 45      
53 minutes or above: round up to 60

Ex: in the first quarter hour, minutes below 8 will be their exact value: 1=1, 2=2, 3=3.

例如:在第一个季度小时,低于8的分钟将是它们的确切值:1 = 1,2 = 2,3 = 3。

When 8 is entered, it is automatically rounded up to 15 (the same holds true for the rest of the hour: ex: 16=16, 17=17, 18=18, 19=19, 20=20, 21=21, 22=22. But 23 through 29 are all rounded up to 30.

当输入8时,它会自动向上舍入到15(其余时间也是如此:例如:16 = 16,17 = 17,18 = 18,19 = 19,20 = 20,21 = 21, 22 = 22.但是23到29都被舍入到30。

Ideally, I could enter both hours and minutes in a single column, ex: 1.54

理想情况下,我可以在一列中输入小时和分钟,例如:1.54

However, I realize that it may be necessary to create a separate column for hours and minutes in order to make this work (i.e., so that my formula is only concerned with rounding up minutes. I can add my hours and minutes together after the minutes are rounded.) Thus:

但是,我意识到可能需要创建一个小时和分钟的单独列,以使这项工作(即,使我的公式只关注四舍五入。我可以在分钟后将我的小时和分钟加在一起四舍五入。)因此:

Column A = Hours (3 hours maximum)   
Column B = Minutes  
Column C = Minutes Rounded up to nearest ¼ hour     
Column D = Col A + Col C

In column B I would like to enter minutes as 1 through 60 (no decimal- i.e., in General, not Time format)

在B列中,我想输入分钟为1到60(无小数 - 即,一般情况下,不是时间格式)

38 minutes in column B would automatically be rounded up to 45 minutes in column C

B列中的38分钟将自动在C列中舍入到45分钟

Does anyone have any ideas? How can I do this using the fewest number of columns?

有没有人有任何想法?如何使用最少的列数来完成此操作?

[A Previously posted question - "Round up to nearest quarter" - introduces the concept of Math.Ceiling. Is this something I should use? I couldn't wrap my head around the answer).

[A之前发布的问题 - “最接近季度” - 介绍了Math.Ceiling的概念。这是我应该使用的东西吗?我无法绕过答案)。

With Grateful Thanks,

感激不尽,

~ Jay

2 个解决方案

#1

How's this go?

怎么回事?

DECLARE @time DATETIME = '2014-03-19T09:59:00'

SELECT CASE 
          WHEN DATEPART(mi, @time) BETWEEN 8 AND 15 THEN DATEADD(mi, 15-DATEPART(mi, @time), @time) 
          WHEN DATEPART(mi, @time) BETWEEN 23 AND 30 THEN DATEADD(mi, 30-DATEPART(mi, @time), @time)
          WHEN DATEPART(mi, @time) BETWEEN 38 AND 45 THEN DATEADD(mi, 45-DATEPART(mi, @time), @time)
          WHEN DATEPART(mi, @time) BETWEEN 53 AND 59 THEN DATEADD(mi, 60-DATEPART(mi, @time), @time)
          ELSE @time
       END

#2

Assume "sessions" is your table (CTE below contains 2 sample records), with session start time & end time stored (as noted in comments above, just store these data points, don't store the calculated values). You might be able to do the rounding as below. (not sure if this is what you want, since it either rounds up or down... do you not want to round down?)

假设“会话”是您的表格(下面的CTE包含2个样本记录),其中存储了会话开始时间和结束时间(如上面的注释中所述,只存储这些数据点,不存储计算值)。您可能能够进行如下四舍五入。 (不确定这是否是你想要的,因为它要么向上或向下舍入......你不想向下舍入吗?)

;WITH   sessions AS (
    SELECT  CAST('20140317 12:00' AS DATETIME) AS session_start, CAST('20140317 12:38' AS DATETIME) AS session_end
    UNION ALL
    SELECT  CAST('20140317 12:00' AS DATETIME), CAST('20140317 12:37:59' AS DATETIME) AS session_end
)
SELECT  *, DATEDIFF(MINUTE, session_start, session_end) AS session_time
        , ROUND(DATEDIFF(MINUTE, session_start, session_end)/15.0, 0) * 15.0 AS bill_time
FROM    sessions;

EDIT:

Hi Jay, I don't think you mentioned it is an Excel problem! I was assuming SQL. As Stuart suggested in a comment above, it would be helpful if you modified your question to indicate it is for Excel, so that others can possibly get help from this dialog in the future.

嗨杰伊,我不认为你提到这是一个Excel问题!我在假设SQL。正如Stuart在上面的评论中所建议的那样,如果你修改了你的问题以表明它是用于Excel,那将是有帮助的,以便其他人可以在将来从这个对话框获得帮助。

With Excel, you can do it with two columns that contain the session start date and time (column A) and session end date and time (column B), plus two formulas:

使用Excel,您可以使用包含会话开始日期和时间(列A)和会话结束日期和时间(列B)的两列,以及两个公式:

Column C (Actual Minutes) = ROUND((B1-A1) * 1440,0)
Column D (Billing Minutes) = (FLOOR(C1/15, 1) * 15) + IF(MOD(C1,15) >= 8, 15, MOD(C1,15))

This is what my table looks like:

这就是我的表格:

3/18/2014 12:00 3/18/2014 12:38 38  45
3/18/2014 14:00 3/18/2014 14:37 37  37

#1