Time arithmetic problem... 1

[Terence]

Hi All , i need help to subract two diiferent times. I have looked for help with this on other pages but have not been able to find the correct VBA to give me the correct results.
I am presentlty taking the time that is put into two different cells and then extracting these and then i want to find the difference between them in the time format.
Here is what i have so far.....

Dim MyTimeBefore, MyTimeFault as integer
Dim TTime as Date

MyTimeBefore = Activecell.offset (-1,-2 ).Range ("A1&quot

MyTimeFault = Activecell.offset (0,-1).Range ("A1&quot

TTime = MyTimeFault - MyTimeBefore

 

[MichaelRed]

? and you were expecting ?

The "formula" is (?probably?) correct. Your expectations appear to be amiss.

The difference is (?possibly?) in understanding that Date/Time types in MS are in reality just specialized interpertion of floating point (e.g. DECIMAL) numbers. In the MS interpertation, the Integer part of the number is the number of (FULL?) days offset from their beginning of time (Dec 30, 1898). The decimal (fraction) part of the value is the time. Unfortunatly (for you), MS decided that this could/would not be in any "standard" time domain, and adopted the convention that it would repressent the fractional part of the 'day'. so, when you perform you calculation -based on two times in the same day- your results are the 'fraction' of 24 hours. For example, if your times are ~~ MyTimeFault = #11/7/2001 5:00 PM# and
MyTimeBefore = #11/7/2001 8:00 AM#, then TTime ~~ 0.35. This, to you, appears to make NO sense, as you are expecting something like 9 Hours.

So, you need to adjust enhance your knowledge of (MS) datatypes as well as your thinking about the way to interpert these results.


TTime = MyTimeFault - MyTimeBefore
MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Terence]

Thanks for the quick response MichaelRed , and like you say i am trying to enhance my knowledge of VBA but this one has got me stumped so far.....

The result that i am looking for is taking two times and in most cases it is the same day and subracting them to get the time diference between them in HH:MM:SS .

I have come across this info pertaining to doing what i need, but have not been able to decifer this. I try to use the =IF and get a fault in VBA as this doesn't look correct but don't quite understand what is missing.




" You can determine the number of hours and minutes between two times by
subtracting the two times. However, since Excel cannot handle negative times,
you must use an =IF statement to adjust the time accordingly. If your times were
entered without a date (e.g, 22:30), the following statement will compute the
interval between two times in A1 and B1 .

=IF(A1>B1,B1+1-A1,B1-A1)

The "+1" in the formula causes Excel to treat B1 as if it were in the next day, so
02:30-22:00 will result in 4:30, four hours and thirty minutes, which is what we
would expect. "

any more thoughts on this?

 

[MichaelRed]

WEll, IF you want to be FORMAL, look up Format.

Something like:

EndTime = #11/7/2001 5:03:14 PM#
StartTime = #11/7/2001 11:59:12 PM#

? Format(Endtime - StartTime, "Short time&quot
06:55

? Format (endtime - StartTime, "hh:mm:ss&quot
06:55:58

? Format(StartTime - EndTime, "h:m:s&quot
6:55:58

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Terence]

Thanks Mike, thats the help i needed.........