Subtracting two time values (1 Viewer)

PaulA · Apr 22, 2009

I am wanting to subtract two time values: Time-1 minus Time-2. In some instances Time-1 will be less than Time-2, other times it will be greater than Time-2 and at other times both will be the same.

I want to create categories of the differences (less than 15 minutes, between 15 and 30 minutes, etc). I also want to know if the value indicates a positive or negative difference.

I am having trouble with this. I have tried formatting the outcome as Short Time, as I do not understand the actual outcome given (such as ".0416666666666667" for one hour). Formating the outcome as time removes the negative or positive aspect of the outcome. I have no problem when Time-1 is greater than Time-2 (a positive difference) but I can't get a negative difference to work.

Any ideas on how to do this?

thanks.

Paul

RuralGuy · Apr 23, 2009

Here's a couple of links that might help remove some of the mystery.
Calculate differences in time
Storing, Calculating, and Comparing Date/Time Data

honda882000 · Apr 23, 2009

Would Format(time_1 - time_2, "Hh:Nn:Ss") work for you?

PaulA · Apr 23, 2009

Thanks for the input. It works well.

I see that the result does not show if the difference is positive or negative. I tried creating a second column giving a code indicating whether time-1 was less than time-2 or vise versa. This will have an impact if I do another query to categorize the outcomes using iff statements (iif(outcome> 0 and outcome > 15 and column X = 2, 1),for example). This works for categorizing the positive outcomes but not for the negative outcomes.

Am I making sense, and, if so, any thoughts?

Thanks.

honda882000 · Apr 23, 2009

Will you always be comparing the seconds only? If you need positive and negative values returned, you might want to use DatePart("s", t1-t2) instead, which will return a number instead of a string.

If it is only seconds you are interested in, would this work for you? :

Code:

Function time_diff(t1 As Date, t2 As Date) As Double

    Dim t_diff As Double 'Time Difference

    t_diff = DatePart("s", t1 - t2)
    
    If t1 > t2 Then 'Time-1 > Time-2, so result is positive
        time_diff = t_diff
        
    ElseIf t1 < t2 Then 'Time-1 < Time-2, so result is negative
        time_diff = -1 * t_diff
    
    Else 'Time-1 = Time-2, so difference = 0
        time_diff = 0
    
    End If

End Function

PaulA · Apr 23, 2009

Interesting way to go but I think your idea will work for me.

Thanks.

honda882000 · Apr 23, 2009

I just found a flaw though, when time2 is greater than time1, but the seconds are the same. For example:

?time_diff(CDate("01:00:50 PM"),CDate("01:10:50 PM"))

This returns 0, since both have 50 for seconds, but the t2 parameter is obviously greater. You might have to return something other than just secs.

honda882000 · Apr 23, 2009

Ok, there has to be an easier way, but I believe this will get you the number of seconds difference between two times. This should also give you positive or negative amounts:

Code:

Function time_diff(t1 As Date, t2 As Date) As Double
'Gets second difference in hour +  difference in minute + difference in seconds

    time_diff = ((DatePart("h", t1) - DatePart("h", t2)) * 3600) + _
                ((DatePart("n", t1) - DatePart("n", t2)) * 60) + _
                    (DatePart("s", t1) - DatePart("s", t2))
 
End Function

aliwesker · Jul 17, 2017

Just multiply by ([time1]-[time2])*24

The_Doc_Man · Jul 17, 2017

Ali ... you DO realize that you replied to a thread last updated in 2009?

aliwesker · Jul 17, 2017

Yes, i was contemplating about it too.
But in my defence I had this problem in the morning today till search engine brought me here. And I see all this complicated solutions for my basic brain, and decided to find an easier way. When found it works couldn't resist to share it. Hey I am thankfull of all who raised this conversation back in 09 and helped to solve a problem of someone like me in 2017. So I signed up and that was my first post.

The_Doc_Man · Jul 17, 2017

Well, in that case, Ali, welcome and keep searching for those nuggets of information that are strewn all over this site!

And for the record, since the question was about seconds, it would be

([Time2]-[Time1]) * 86400

Your formula gives hours.

Subtracting two time values (1 Viewer)

PaulA

Registered User.

RuralGuy

AWF VIP

honda882000

Registered User.

PaulA

Registered User.

honda882000

Registered User.

PaulA

Registered User.

honda882000

Registered User.

honda882000

Registered User.

aliwesker

New member

The_Doc_Man

Immoderate Moderator

aliwesker

New member

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread