On Tue, 5 Jul 2005 08:35:09 -0700, "kasey"
wrote:

I am calculating the difference between two dates and times. For example,
6/3/2004 14:50 - 6/1/2004 12:00. The formula I am using now is
=text(a1-a2,"m, d, hh:mm"). When this formula calcuates the month
(especially in the example I just gave), the value for month is shown as one
more than what the answer should be. This is what is produces: 6/3/2004
14:50 - 6/1/2004 12:00 = 1, 2, 14:50. It should equal 0, 2, 14:50. The time
is important especially when calculation over a 24 hr period.

Any other ideas?

In addition to not responding to my previous questions, I don't understand how
you obtain any of the results that you have posted.

In your first example, (31/10/2004 18:00) - (01/09/2004 07:00) it seems to me
the correct answer should be 1 month 30 days and not the 1 month 29 days that
you posted.

In your second example, although your dates are ambiguous I must assume that
you are using the same English format as in your initial posting
(day-month-year) so I don't understand why you expect an answer of 0, 2, 14:50

I also do not understand how you subtract 12:00 from 14:50 and get 14:50.

I would expect an answer of 2 m 0 d 02 h 50 min

If you could clear up these discrepancies, and also respond to the questions I
previously posted, I'm sure we could provide you with a solution.

I obtained the above results with the earlier date in A2, and the later date in
A1, using the formula:

=DATEDIF(INT(A2),INT(A1-(MOD(A1,1)<MOD(A2,1))),"m")&
" m " & DATEDIF(INT(A2),INT(A1-(MOD(A1,1)<MOD(A2,1))),"md")
& " d " & TEXT(MOD(A1,1)-MOD(A2,1)+(MOD(A1,1)<MOD(A2,1)),"hh \h mm \m\i\n")

BUT, there are certain dates for which this formula will misbehave and give
illogical answers. That is why we need the discrepancies cleared up and my
previous questions answered.



--ron