Click here to Skip to main content
15,947,389 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 13-Oct-17 8:46am
v2
Comments
ZurdoDev 22-Jun-17 7:43am    
Sub Macro1()
'
' Macro1 Macro
'

'

Dim startDateTime As Date, endDateTime As Date
startDateTime = "2017/06/22 10:00 AM"
endDateTime = "2017/06/22 3:00 PM"

Dim diff As Date
diff = Format(endDateTime - startDateTime, "hh:mm:ss")
End Sub


diff gives me 5:00:00 AM.
planetz 22-Jun-17 12:05pm    
I have converted strings into date using CDate(). Could that be a problem?
Richard Deeming 22-Jun-17 14:59pm    
Can you show us an example of the input, the expected output, and the actual (wrong) output?
planetz 23-Jun-17 3:34am    
Yes, of course.

inputs:
date1=20-06-2017 01:02:30 am
date2=21-06-2017 01:04:30 am

after subtracting date2-date1, output
00:02:00

It is only calculating the minutes and seconds and not the hours.

My code:

Dim startDateTime, endDateTime As Date

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

Form.txtDuration.Text = Format((endDateTime - startDateTime), "hh:mm:ss")

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")
 
Share this answer
 
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
 
Share this answer
 
Use DateDiff()[^] function.

VB
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


Note: i've used YYYY-MM-DD notation of date (ISO format[^]).
 
Share this answer
 
Quote:
inputs:
date1=20-06-2017 01:02:30 am
date2=21-06-2017 01:04:30 am

after subtracting date2-date1, output
00:02:00

It is only calculating the minutes and seconds and not the hours.

This is Excel formatting.
The result is 1 day and 0 hours and 2 minutes, since you want to display only hours and minutes, the display is correct.
[Update]
To get result with more than 24 hours, you need to build your own formatting.
Try to use "[h]" excel formatting to get more than 24 hours.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900