Elapsed time

Greetings,

I am trying to do a formula and I am driving myself mad. Here is what I am trying to do hoping someone could help me. I have 2 columns of dates in the format of "1/4/05 14:04" I want to find the elapased time between the two columns. With a result of something like "4 days, 2 hours, 12 minutes" Can anyone help me figure this out? Any help would be appreciated guys.

Regards,
cd

Try the following...

=INT(B1-A1)&" days, "&INT(24*MOD(B1-A1,1))&" hours, and "&ROUND(60*MOD(24*(B1-A1),1),0)&" minutes"

...where A1 contains the start time and B1 contains the end time.

Hope this helps!

Originally Posted by relux

Greetings,

I am trying to do a formula and I am driving myself mad. Here is what I am trying to do hoping someone could help me. I have 2 columns of dates in the format of "1/4/05 14:04" I want to find the elapased time between the two columns. With a result of something like "4 days, 2 hours, 12 minutes" Can anyone help me figure this out? Any help would be appreciated guys.

Regards,
cd

Excellent, exactly what I am looking for. Now how can I get every single field to do this. I have the start date/time in C and end date/time in D. I would like results in F. How can I paste this formula all the way down F without having to reenter and change the cells in the formula? Hope that makes sense... :/

Thanks again!

1) Enter the following formula in F1:

=INT(D1-C1)&" days, "&INT(24*MOD(D1-C1,1))&" hours, and "&ROUND(60*MOD(24*(D1-C1),1),0)&" minutes"

2) Move your cursor over the bottom right corner of cell F1 (cursor should change to something like a cross, I think)

3) Click and drag down

OR

1) Select/click F1

2) Edit > Copy

3) Select the range of cells to paste the formula

4) Edit > Paste

Hope this helps!

Originally Posted by relux

Excellent, exactly what I am looking for. Now how can I get every single field to do this. I have the start date/time in C and end date/time in D. I would like results in F. How can I paste this formula all the way down F without having to reenter and change the cells in the formula? Hope that makes sense... :/

Thanks again!

You guys are great, that is what I was looking for. One last thing.. how can i get an average of the elapsed time?

Thanks!

I haven't fully tested it, but try the following formula...

=AVERAGE(IF(D1:D10<>"",INT(D1:D10-C1:C10)))&" days, "&AVERAGE(IF(D1:D10<>"",INT(24*MOD(D1:D10-C1:C10,1))))&" hours, and "&AVERAGE(IF(D1:D10<>"",ROUND(60*MOD(24*(D1:D10-C1:C10),1),0)))&" minutes"

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Originally Posted by relux

You guys are great, that is what I was looking for. One last thing.. how can i get an average of the elapsed time?

Thanks!

Originally Posted by Domenic

I haven't fully tested it, but try the following formula...

=AVERAGE(IF(D1:D10<>"",INT(D1:D10-C1:C10)))&" days, "&AVERAGE(IF(D1:D10<>"",INT(24*MOD(D1:D10-C1:C10,1))))&" hours, and "&AVERAGE(IF(D1:D10<>"",ROUND(60*MOD(24*(D1:D10-C1:C10),1),0)))&" minutes"

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

It doesnt appear to work. I really wish I knew this stuff. Shame on the boss for throwing an excel project at me! I get a #VALUE field.

thanks, again

Originally Posted by relux

It doesnt appear to work. I really wish I knew this stuff. Shame on the boss for throwing an excel project at me! I get a #VALUE field.

thanks, again

Make sure that you confirm with CONTROL+SHIFT+ENTER, not just the usual ENTER.

Therefore, after typing your formula, hold the CONTROL and SHIFT keys down, then while both are pressed, hit the ENTER key. Excel willl place braces {} around the formula, indicating that you've entered the formula correctly.

Originally Posted by Domenic

Make sure that you confirm with CONTROL+SHIFT+ENTER, not just the usual ENTER.

Therefore, after typing your formula, hold the CONTROL and SHIFT keys down, then while both are pressed, hit the ENTER key. Excel willl place braces {} around the formula, indicating that you've entered the formula correctly.

Did that, it appears it was entered correctly. I see the braces...

Originally Posted by relux

Did that, it appears it was entered correctly. I see the braces...

First, let's change the formula I gave you to the following...

=INT(AVERAGE(IF(D1:D10<>"",D1:D10-C1:C10)))&" days, "&INT(24*MOD(AVERAGE(IF(D1:D10<>"",D1:D10-C1:C10)),1))&" hours, and "&ROUND(60*MOD(24*(AVERAGE(IF(D1:D10<>"",D1:D10-C1:C10))),1),0)&" minutes"

...confirmed with CONTROL+SHIFT+ENTER. Again, I have not fully tested it, but from my limited testing, it seems to work.

If you're still having problems, make sure that the ranges you reference in your formula all have the same size. Another possibility is that one or more of the cells actually contain a text value. Try the following...

=SUMPRODUCT(--(ISNUMBER(C1:C10)))=ROWS(C1:C10)

AND

=SUMPRODUCT(--(ISNUMBER(D1:D10)))=ROWS(D1:D10)

...adjusting it for your actual range. What result do you get?

Originally Posted by Domenic

First, let's change the formula I gave you to the following...

=INT(AVERAGE(IF(D1:D10<>"",D1:D10-C1:C10)))&" days, "&INT(24*MOD(AVERAGE(IF(D1:D10<>"",D1:D10-C1:C10)),1))&" hours, and "&ROUND(60*MOD(24*(AVERAGE(IF(D1:D10<>"",D1:D10-C1:C10))),1),0)&" minutes"

...confirmed with CONTROL+SHIFT+ENTER. Again, I have not fully tested it, but from my limited testing, it seems to work.

If you're still having problems, make sure that the ranges you reference in your formula all have the same size. Another possibility is that one or more of the cells actually contain a text value. Try the following...

=SUMPRODUCT(--(ISNUMBER(C1:C10)))=ROWS(C1:C10)

AND

=SUMPRODUCT(--(ISNUMBER(D1:D10)))=ROWS(D1:D10)

...adjusting it for your actual range. What result do you get?

Works great! I forgot my cell C and D dont start till row 3. All is great, thanks so much!