Excel Time calculation

[drkestrel]

I would like to do some time calculation as follows

     Col B         Col C           Col D
Row1 StartTime     EndTime         Time elapsed
Row 2......

Namely, I want for D29999 to be Cx-Bx, where x is the current row.

Problem is Col B and Col C is of format hh:mm:ss

I would like the output in Col D to be of format minutes (i.e.)

     Col B         Col C           Col D
Row1 StartTime     EndTime         Time elapsed
Row2 11:23:00      12:26:00        63minutes

If I try to set format of Col D to be mm, output would be 3 minutes and not 63 minutes!!

Any simple way of doing this?

 

[Klae]

I take it you don't want to set the format to hh:mm which will say 01:03 (C2 value is "=sum(A2 - B2)&quot?

 

[drkestrel]

No I want the number to be in minutes only

 

[dsi]

Format the elapsed time cell as Custom > [mm]
Then, (D1 = C1 - B1) will give you 63 minutes.

 

[drkestrel]

If I then want to use the results of the Elapsed minutes in another calculation, how could the value be treated as a nunmber?
e.g. Have Col E- number processed/minute= Col A (number)/Col D (Elapsed)

 

[svanels]

Use the hour and the time fuctions in Excel
in D2 write =Minute(C2-B2)

Be sure that the formate in D2 is number, else the display will be 0:00 or 12:00 AM S. van Els
SAvanEls@cq-link.sr

 

[dsi]

I thought that it would be used as a number, but it doesn't seem to work that way. I am sure that Dreamboat can solve this problem. I would cheat and write my own function.

Change the format of column D (elapsed time) to the Number Format. Then, use this formula instead:

=TEXT(C3-B3,"[mm]&quot

- Go to the VB Editor
- Insert a Module
- Add this code:

Public Function CalculateRate(rQty As Long, sTime As String) As Single
    CalculateRate = rQty / CInt(sTime)
End Function

Then, use this function in column E
=CalculateRate(A3,D3)

 

[JVFriederick]

B C D
2 11:23:00 12:26:00 63.00
3 12:26:00 14:55:00 149.00

Formula in column D =(+C2-B2)*24*60
Formula in column D =(+C3-B3)*24*60

Excel calulates time of day on 24 hour basis

You can use Round function if necessary.