0

I've got 2k rows of time (from a Toshiba fax log) in two different formats

  • 1:10 = 1min 10sec
  • 27:55:00 = 27min 55sec 0ms

I want to total/sum this time column however it calculates wrong (5:05 instead of the actual 29:05) maybe because it thinks the second time format is hours? I can't seem to format or convert these in a second column with a formula so I can get a grand total.

1 Answer 1

3

If excel is seeing them as times then simply divide by 60:

=A1/60

then sum the results.

enter image description here

3
  • Gosh what an easy fix, a follow-up question if you can: how to convert the subtotal so its just minutes in number (decimal or integer) format? So in your example it'd be 29 rounded
    – gregg
    Jul 12, 2019 at 21:52
  • Scratch that I got it: =HOUR(B3)*60+MINUTE(B3)+SECOND(B3)/60
    – gregg
    Jul 12, 2019 at 22:00
  • @gregg , Without a helper column it could be =SUM(A1:A2)/60. For a number it would be: =(SUM(A1:A2)/60)*1440
    – JvdV
    Jul 12, 2019 at 22:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .