Formula and format for time duration

[skarp]

Employees are entering their daily time spent on activities in minutes:

Monday
Act A: 45
Act B: 120
Act C: 30

How do I get a daily total that would look like this:
3:15 (to show 3 hours and 15 mins), instead of 3.25

Thanks!

 

[barry houdini]

If the minutes are in A1:A10 then try this formula

=SUM(A1:A10)/1440

format result cell with the custom format [h]:mm

 

[VoG]

Try this. The result cell is formatted as hh:mm. If the total duration could exceed 24 hours use [h]:mm.

<b>Sheet11</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">45</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">120</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">30</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">03:15</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A4</td><td >=SUM(A1:A3)/1440</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[skarp]

Great, that worked. What does the 1440 represent? and what is the difference between [h:mm] and [hh:mm]?

 

[VoG]

Excel stores times/durations as fractions of a day. 1440 is the number of minutes in a day (24 * 60) so dividing minutes by 1440 gives an Excel time.

hh:mm will display hours and minutes but won't display durations greater than 23:59. To display durations of 24:00 and above you need [h]:mm.

 

[skarp]

got it. thanks for the prompt and clear responses!