格式化日期差异hh：mm：ss in excel VBA [英] Format date difference in hh:mm:ss in excel VBA

问题描述

Hello All，



我正在尝试使用excel vba格式hh：mm：ss格式化日期差异。 1小时20分钟将是01:20:00，30分40秒将是00:30:40等等。



日期格式

dd-mm-yyyy hh：mm：ss AM / PM



我尝试过：



diff =格式（endDateTime - startDateTime，hh：mm：ss）





我试过这个公式。但它似乎没有给出正确的答案。

Hello All,

I am trying to format date difference in the format hh:mm:ss in excel vba. "1hr 20 mins" will be "01:20:00", "30 mins 40 sec" will be "00:30:40" and so on.

Dates are in format
dd-mm-yyyy hh:mm:ss AM/PM

What I have tried:

diff = Format(endDateTime - startDateTime, "hh:mm:ss")


I have tried this formula. But it does not seem to give the correct answer.

推荐答案

问题是只有 hh 格式说明符包括不算作天数的小时数。一旦你超过24小时，事情变得复杂一点。



尝试这样的事情：

The problem is that the hh format specifier only includes hours which are not counted as days. Once you go over 24 hours, things get a bit more complicated.

Try something like this:

Dim startDateTime As Date, endDateTime As Date
Dim diff As Double, totalHours As Long

startDateTime = CDate(Form.txtStartTime.Text)
endDateTime = CDate(Form.txtEndTime.Text)
diff = endDateTime - startDateTime

totalHours = CLng(24 * diff)

Form.txtDuration.Text = Format(totalHours, "00") & ":" & Format(diff, "mm:ss")

您可以使用此功能：

You can use this function:

Public Function FormatHourMinuteSecondDiff( _
  ByVal datTimeStart As Date, _
  ByVal datTimeEnd As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
  
' Returns count of days, hours, minutes and seconds of difference
' between datTimeStart and datTimeEnd converted to
' hours and minutes and seconds as a signed formatted string
' with an optional choice of time separator.
'
' Example:
'   datTimeStart: #10:03:02#
'   datTimeEnd  : #20:01:18#
'   returns     : 9:58:16
'
'   datTimeStart: #22:13:34#
'   datTimeEnd  : #3:45:47#
'   returns     : -18:27:47
'
' 2007-11-05. Cactus Data ApS, CPH.
  
  Const cintSecondsHour As Integer = 60 * 60
  
  Dim lngSeconds    As Long
  Dim strHourFull   As String
  Dim strHourPart   As String
  Dim strHMS        As String
  
  lngSeconds = DateDiff("s", datTimeStart, datTimeEnd)
  strHourFull = CStr(lngSeconds \ cintSecondsHour)
  ' Format minute and second part.
  strHourPart = Format(TimeSerial(0, 0, Abs(lngSeconds) Mod cintSecondsHour), "nn\" & strSeparator & "ss")
  strHMS = strHourFull & strSeparator & strHourPart
  
  FormatHourMinuteSecondDiff = strHMS
  
End Function

使用 DateDiff（） [ ^ ]功能。

Use DateDiff()[^] function.

Dim date1 As Date = CDate("2017-06-20 08:05:30 PM")
Dim date2 As Date = CDate("2017-06-21 01:04:30 AM")

Dim totalminutes As Integer = DateDiff("n", date1, date2)
Dim hours As Integer = totalminutes /60
'the rest of dividing
Dim minutes As Integer = (totalminutes Mod 60)

Dim sMessage = totalminutes & " minutes = " & hours & " hours " & minutes & " minutes"
MessageBox sMessage, vbinformation, "Information"

结果：

Result:

299 minutes = 4 hours 59 minutes

注意：我使用过 YYYY-MM- DD 日期符号（ ISO格式 [ ^ ]）。

Note: i've used YYYY-MM-DD notation of date (ISO format[^]).

这篇关于格式化日期差异hh：mm：ss in excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！