从日期和时间计算时间 [英] Time calculation from Date & Time

问题描述

我试图找出一种方法，我可以使用 MS 访问转换以下数据以显示每列中花费的分钟数，同时还从最终数字中扣除周末.该数据是从项目管理软件中提取的，其中任务在通过相关校准阶段时通过每一列传递(因此数据格式如图所示).此表还将存储经过此过程的所有产品的转换数据.

I am trying to figure out a way where I can use MS access to convert the the following data to show the minutes spent within each column, whilst also discounting weekends from the final figure. This data is pulled from a project management software where the task is passed through each column as it goes through the relevant stage of calibration (hence the data format as shown). This table will also store the transition data for all products that go through this process.

此计算是否需要在附加到此表之前完成，还是可以在用户希望查看项目在每列中花费多长时间时即时完成?我正在努力理解如何获得查询以检查上面的行以测量时间差异.请帮忙.

Will this calculation need to be done prior to appending to this table or can this be done on the fly whenever the user wishes to see how long the item spent in each column? I am struggling to understand how I can get a query to check the row above to measure the difference in time. Please help.

(为 Gustav 添加)

(Added for Gustav)

推荐答案

类似:

Select 
    *, 
    (Select Max(transitionDate) 
    From YourTable As T 
    Where T.product = YourTable.product And T.transitionDate < YourTable.transitionDate)
    - transitionDate As transitionTime
From
    YourTable

时间输出将是您可能具有格式的天数，例如小时和分钟:

The time output will be days which you may have format, like hours and minutes:

Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String

' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String

  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute

  FormatHourMinute = strHourMinute

End Function

这篇关于从日期和时间计算时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！