How to calculate "exact elapsed time" difference between two times

[Mahendra S]

Dear Forum members,

Greetings!

Please see if anyone know a solution / can helpfor below objective:
{Scenario1 is working for quick-solution and Scenario2 is not with attachment}

Objective: How to calculate "exact elapsed time" difference between two times
with two following scenario's
Scenario1: If starttime and endtime are on the same day with in 24 hours
Scenario2: If starttime and endtime are on different day's
(For example start time before midnight & end time over midnight)

Solution: Need a procedure call with Function(s) in excel using VBA

Please refer attachment "ElapsedTimeCalculation.xlsm" {A Microsoft Excel Macro Enabled Worksheet} for the code reference related to above Two scenario's.
If any one know what is incorrect in Scenario2 related Function, please correct and let me know,
{Please note, you can view the code - Close the user-form, right-click on work-sheet(ElapsedTimeCal) ->View Code of UserForm1}

Appreciate your time and help in advance regarding the above solution need, thank you!

Best,
Mahendra

 

[Marc L]

Hi,​

​

if each value respects the Excel date numeric format aka Date & Time so this is just a normal operation like EndTime - StartTime​

and you can easily check yourself with an easy cell formula and an appropriate cell formatting as [hh]:mm …​

​

 

[Mahendra S]

Hi Marc,

Thanks for responding, however I'm not using Cell Formula's in this case to figure out "elapsed time in minutes between two different dates" I had hard coded values "to make it simple to understnad" for both scenario's as mentioned above.

a simple VBA code with macro helps, however the problem is in second Function as below:

>>> use code - tags <<<

'----------------------------------------------------------------------------
'Function2 to find "minutes" between elapsed days (Before-and-After midnight) with time elapsed time interval
'(dataset2 - for example: starttime = "11:59:00" & endtime = "12:01:00" after midnight )
'-------------------------------------------------------------------------------
Function ElapsedTimeDiff(StartTime As Date, EndTime As Date)

        ElapsedTimeDiff = DateDiff("n", EndTime, StartTime) / 60 
        '- Expected result = 2, but Actual result showing as = -24
            
End Function

please note, a normal operation with below line of code "is in-corrrect with the expected result.
ElapsedTimeDiff = Abs(EndTime - StartTime)
'- Expected result = 2, but Actual result showing as = 1

Thank you!

 

[vletm]

Mahendra S
#1 Check DateDiff's syntax.
... Where do You need /60?
... after that, I could get Your expected result 2.
#2 Do You use 12- or 24-hour times?
#3 As in Mark L's #2 reply - there should be always date and time, if You would like to calculate always exact result.
... How do You take care if difference of times is over 24 hrs? eg from 09:00 to 10:00 ( next day )

 

[Marc L]

for example: starttime = "11:59:00" & endtime = "12:01:00" after midnight
Expected result = 2

According to Excel basics - a day as date / time unit - a VBA demonstration for a beginner starter :​

Sub Demo1()
    Const S = #11:59:00 PM#, E = #12:01:00 AM#
    Debug.Print (E - (E < S) - S) * 1440
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Marc L]

Another VBA demonstration according to date / time :​

Sub Demo2()
    Const S = #1/13/2023 11:59:00 PM#, E = #1/14/2023 12:01:00 AM#
    Debug.Print CSng(E - S) * 1440
End Sub

You may Like it !​

​

 

[Marc L]

According to DateDiff function - a VBA help must read ! - a variation of my first demonstration :​

Sub Demo1d()
    Const S = #11:59:00 PM#, E = #12:01:00 AM#
    Debug.Print DateDiff("n", S, E - (E < S))
End Sub

You should Like it !​

​

 

[Mahendra S]

Thank you Marc, the tricky part was resolved with single function which works irrespective of dates (same date or different date) with different "endtime" and "starttime" to get "exact elapsed time" duration between two times.
The Single Function to refer as you had mentioned above in DemoId as below

>>> use code - tags <<<

'----------------------------------------------------------------------------
'Function2 to find "minutes" between elapsed days (Before-and-After midnight) day's time elapsed time interval
'(dataset2 - for example: starttime = "11:59:00" & endtime = "12:01:00" after midnight )
'-------------------------------------------------------------------------------
Function ElapsedTimeDiff(EndTime As Date, StartTime As Date)

        '----Working Function----
        ElapsedTimeDiff = DateDiff("n", StartTime, EndTime - (EndTime < StartTime))
        '- Expected result = 2, & Actual result = 2 which is correct
       
End Function

'-------------------------------------------------------------------------------
Note: I also figured out another way of using with "CSng" (like you had mentioned in Demo2) to find duration with Days/Hours/Minutes/Seconds

Int(CSng(recDuration)) & " Days " & _
                Format(recDuration, "hh") & " Hours " & _
                Format(recDuration, "nn") & " Minutes " & _
                Format(recDuration, "ss") & " Seconds"
'-------------------------------------------------------------------------------

Thanks again, have a nice day!