Excel VBA: How To Calculate Days, Hour & Minute Between 2 Dates

[kashif.special2005]

Hi,

I have 2 dates with time stamp in an Array variable and I want to calculate Days, Hours And Minutes between the 2 dates,

Below is the example:

Start Date: 1/1/2021 12:24:00 PM
End Date: 12/31/2021 2:15:02 PM

And the result should be as below.

Result: 364 Days 1 Hour 51 Minutes

Thanks in advance for all the help.

Thanks
Kashif

 

[Jimmypop]

Hi there

Will the below formula route work? You would need to look at your cell formatting though....

Book1
	A	B
1	Start	01/01/2021 12:24
2	End	31/12/2021 14:15
3	Total	364 Days 1 Hour 51 Minutes
Sheet1

Cell Formulas
Range	Formula
B3	B3	=INT(B2-B1)&" Days "&HOUR(MOD(B2-B1,1))&" Hour "&MINUTE(MOD(B2-B1,1))&" Minutes"

 

[Flaiban]

Try this code :

Sub Date_Dif()

    Dim D1 As Date
    Dim D2 As Date
    D1 = Range("A1").Value
    D2 = Range("A2").Value

    Dim diff As Date
    diff = D2 - D1

    ActiveSheet.Range("A3").Value = _
               CLng(Int(diff)) & " days " & _
               Hour(diff) & " hours " & _
               Minute(diff) & " minutes " & _
               Second(diff) & " seconds"
End Sub

 

[Phuoc]

Try this

Book1
	A	B	C
1	Start Date:	End Date:	Result:
2	1/1/2021 12:24	12/31/2021 14:15	364 day(s) 1 Hour(s) 51 Minute(s)
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INT(B2-A2)&" day(s) "&TEXT(B2-A2,"h"" Hour(s) ""mm"" Minute(s)""")

 

[kashif.special2005]

Thank you so much all of you for your precious time to solve this question.

I am using Phuoc method to solve this issue, below is the code that I am using to solve this issue.

Sub Calculate_DaysHoursMinute_From_TwoDates()

    Dim sDays As String, sHour As String, sMinute As String
    Dim vMin As Date, vMax As Date
    Dim vResult As Variant
    
    vMin = Range("A2").Value  'StartDate
    vMax = Range("B2").Value  'EndDate
    
    'INT(B2-A2)&" day(s) "&TEXT(B2-A2,"h""  Hour(s) ""mm"" Minute(s)""")
    Debug.Print Int(vMax - vMin) & " day(s) " & Format(vMax - vMin, "h"" Hour(s) ""m"" Minute(s)""")
    
End Sub

Thank you so much again all of you.

Thanks
Kashif