On Wed, 6 Jul 2005 11:30:05 -0700, "kasey"
wrote:

Ron,

Thank you for taking the time to help me out. I am trying to calculate the
difference between two dates and times so that I can put them into
classifications from hours to days. I need to use both date and time for
this because it considers the 24 hr clock that is needed for this calculation
to work.

For example:

A1: 4/18/05 15:23
A2: 4/17/05 15:05

If I am using my current formula of =text(A1-A2,"d, hh:mm"), then the result
should be 1, 00:18.

Here is where I end up having the issue:

A1: 10/1/04 10:55
A2: 7/28/04 17:45

When using the formula =text(A1-A2, "m, d, hh:mm"), the result is 3, 4,
17:10. It should be 2, 4, 17:10. The same problems happens if/when I was to
use this same formula for the first example. Obivously, the first example
formula doesn't give me all the information I need.

Does this clear up some of your questions? Or did I just re-iterate what I
had said before?

Thanks again for your time!


It doesn't really clear up my questions but perhaps my discussion below will
clarify the information required.

First of all, your formula will not give the results you expect (as you have
already discovered).

It appears as if now, unlike your first example, you are now using US style
dates.

That being the case, given your expected result in your second example, it
appears as if what you want to do, with regard to counting months, is to count
CALENDAR months and then add on the extra days that fall outside of a whole
calendar month, and also take into account the time.

IS THAT THE CASE???

If it is the case, then for

A1: 25-Oct-2004 10:55
A2: 05-Jul-2004 17:45

I would expect you would want an answer of:

2 months 51 days 17 h 10 min

or, in the notation you are using above 2, 51, 17:10

(The TWO (2) months are Aug and Sep; then we have 25 days, 10 hrs, 55 minutes
in October; and 26 days 6 hrs 15 min in Jul).

If that is NOT the case, then you will have to explain IN WORDS *exactly how*
you are getting the results that you have obtained.

If that IS the case, I have a UDF I can modify slightly that will accomplish
what you want.

Also, when you post dates, if there is possible confusion between date formats,
please be clear as to which format you are using. I usually assume US (m-d-y),
but in your very first example, you were using a d-m-y format.


--ron