Format date difference in hh:mm:ss in excel VBA

Question

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 22-Jun-17 0:22am

planetz

Updated 13-Oct-17 8:46am

RedDk

v2

Add a Solution

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")

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2017-06-23T01:24:00

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")

Gustav Brock · Answer 2 · 2017-06-25T08:24:00

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

Maciej Los · Answer 3 · 2017-10-13T07:28:00

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[^]).

Patrice T · Answer 4 · 2017-10-13T08:46:00

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.

Format date difference in hh:mm:ss in excel VBA

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0