software

I have a spreadsheet that has a date in one column, Time in the next, date
in the next column, then time in teh column after that. I need to find out
how much time elapsed.
For example

In cell A1 the date is 1/9/2006
In cell B1 the time is 10:55

In C1 the date is 1/22/2006
in D1 the Time is 14:00

So I need to find the the elapsed time between these 2 times.Thanks in advance

Here's a few options....

To give elapsed time in hours

=D1 C1-B1-A1

format as [h]:mm

To give the result in days, hours, minutes (if elapsed period will
always be less than 32 days)

format as

d quot;daysquot; h quot;hoursquot; m quot;minsquot;

To give a result in days, hh:mm (for any time period) try this formula

=INT(D1 C1-B1-A1)amp;quot; days quot;amp;TEXT(MOD(D1 C1-B1-A1,1),quot;hh:mmquot;)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=517554
WOrks well for teh most part, but there are a few items that has me
lost.
A couple of places where the dates are teh same, it is giving me a date
difference of -38771, yet the time difference it calculates correctly.

Another place on the sheet, it lists it as a -1 day and 5 hours, 46 min
and 34 sec.

Here are the dates and time for that item:
A1 = 2/16/2006
b1 = 18:45:12
c1 = 2/16/2006
d1 = 19:16:48

As you see, for days, it should say quot;0quot;
and time it should say a little over 30 min.--
johnfli
------------------------------------------------------------------------
johnfli's Profile: www.excelforum.com/member.php...oamp;userid=32043
View this thread: www.excelforum.com/showthread...hreadid=517554Hi:
That's odd; I just tried the same formula with your values and do get the
correct answer 0:31:36 Are you sure you've formatted the cell that will
receive the calculation as custom? Should be [h]:mm:ss

A1 B1 C1 D1 E1
2/16/200618:45:122/16/200619:16:480:31:36

Formula in E1 is D1 C1-B1-A1

Thank you, daddylonglegs: this was exactly the format code I've been
looking for.

Best regards,
LHSallwasser

quot;johnfliquot; wrote:

gt;
gt; WOrks well for teh most part, but there are a few items that has me
gt; lost.
gt; A couple of places where the dates are teh same, it is giving me a date
gt; difference of -38771, yet the time difference it calculates correctly.
gt;
gt; Another place on the sheet, it lists it as a -1 day and 5 hours, 46 min
gt; and 34 sec.
gt;
gt; Here are the dates and time for that item:
gt; A1 = 2/16/2006
gt; b1 = 18:45:12
gt; c1 = 2/16/2006
gt; d1 = 19:16:48
gt;
gt; As you see, for days, it should say quot;0quot;
gt; and time it should say a little over 30 min.
gt;
gt;
gt; --
gt; johnfli
gt; ------------------------------------------------------------------------
gt; johnfli's Profile: www.excelforum.com/member.php...oamp;userid=32043
gt; View this thread: www.excelforum.com/showthread...hreadid=517554
gt;
gt;


johnfli Wrote:
gt; WOrks well for teh most part, but there are a few items that has me
gt; lost.
gt; A couple of places where the dates are teh same, it is giving me a date
gt; difference of -38771, yet the time difference it calculates correctly.
gt;
gt; Another place on the sheet, it lists it as a -1 day and 5 hours, 46 min
gt; and 34 sec.
gt;
gt; Here are the dates and time for that item:
gt; A1 = 2/16/2006
gt; b1 = 18:45:12
gt; c1 = 2/16/2006
gt; d1 = 19:16:48
gt;
gt; As you see, for days, it should say quot;0quot;
gt; and time it should say a little over 30 min.

I don't believe that formula will give incorrect results - it may be
that your times are not as they seem. What do you get if you
temporarily format D1 or B1 as general, you should see a number between
0 and 1, if not this will throw out the formula.

Do you have formulas generating the times or are they just entered
manually?--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=517554The data is entered manualy. I think it must be some funky format in teh
cell becasue I have about 100 rows of different times and dates, and about
95% work just fine.
One the cell where I get teh result of -38753, when I set the cell format to
general, it changes teh date to 38754

quot;daddylonglegsquot; gt;
wrote in message
news:daddylonglegs.240ogy_1141260301.2117@excelfor um-nospam.com...
gt;
gt; johnfli Wrote:
gt;gt; WOrks well for teh most part, but there are a few items that has me
gt;gt; lost.
gt;gt; A couple of places where the dates are teh same, it is giving me a date
gt;gt; difference of -38771, yet the time difference it calculates correctly.
gt;gt;
gt;gt; Another place on the sheet, it lists it as a -1 day and 5 hours, 46 min
gt;gt; and 34 sec.
gt;gt;
gt;gt; Here are the dates and time for that item:
gt;gt; A1 = 2/16/2006
gt;gt; b1 = 18:45:12
gt;gt; c1 = 2/16/2006
gt;gt; d1 = 19:16:48
gt;gt;
gt;gt; As you see, for days, it should say quot;0quot;
gt;gt; and time it should say a little over 30 min.
gt;
gt; I don't believe that formula will give incorrect results - it may be
gt; that your times are not as they seem. What do you get if you
gt; temporarily format D1 or B1 as general, you should see a number between
gt; 0 and 1, if not this will throw out the formula.
gt;
gt; Do you have formulas generating the times or are they just entered
gt; manually?
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=517554
gt;