在Access VBA中将秒转换为小时分钟格式 [英] Converting Seconds to Hours Minutes Seconds Format in Access VBA

问题描述

我正在尝试将我的数据转换为以小时，分钟和秒为单位显示.我目前仅在几个小时内显示数据，但是由于我格式化的方式，它正在向上或向下取整.

I am trying to convert my data to be shown in Hours, Minutes, and Seconds. I currently have the data being shown in just hours, however, it is rounding up or down due to the way I have it formatted.

我要从中提取的服务器在几秒钟内提取了数据，因此我只需做一下(Sum(AuxTime)/3600.0)就将其转换为小时.但是我需要[H]:mm:ss格式.

The server that I am pulling from pulls the data in seconds, so I have converted it to hours by just simply doing (Sum(AuxTime)/3600.0). But I need it in [H]:mm:ss format.

我尝试过然后直接从Access查询(AuxTime)中的服务中提取数据.然后在我的VBA模块中，我将数字格式设置如下:

I tried then just pulling the data straight from the serve in my Access query (AuxTime). Then in my VBA module, I made the number format as follows:

 With .PivotFields("Aux_Time")
    .Orientation = 4
    .Function = -4157
    .NumberFormat = "[h]:mm:ss"
    .Caption = "Time Spent in Aux Time"
 End With

但是，并不是将数据以这种格式放置，而是将所有秒数放置在小时区域中.

However, that is not putting the data in that format, it is just putting all of the seconds in the hours area.

还有其他我想念的东西吗?

Is there anything else that I should be doing that I am missing?

推荐答案

您将需要一个自定义函数.但是，首先将秒转换为时间值(每天86400秒)，然后将其格式化为文本:

You will need a custom function for this. But, first convert the seconds to a time value (86400 seconds per day), then format to text:

Display = FormatHourMinuteSecond(CDate(Sum(AuxTime) / 86400))

使用如下函数:

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

' Returns count of days, hours, minutes, and seconds of datTime
' converted to hours, minutes, and seconds as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03:55# + #20:01:24#
'   returns: 30:05:19
'
' 2014-06-17. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinuteSec  As String
  Dim strHours      As String

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

  FormatHourMinuteSecond = strHours

End Function  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute

  FormatHourMinute = strHourMinute

End Function

这篇关于在Access VBA中将秒转换为小时分钟格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！