Converting hh:mm:ss to minutes in excel

  VNAM75 11:03 21 May 2007

I have a cell with the value


I need it rounding to the nearest value in minutes ie. 120+37=157

I have tried all the formatting options I can think of.

  VoG II 11:07 21 May 2007

With the time in A1


and format the result cell as Number.

  VNAM75 11:14 21 May 2007

Thanks VoG™. Just what was needed.

  conrail 11:21 21 May 2007


  VNAM75 11:32 21 May 2007

Pressed resolved too quick! I have another value


It is formatted as hh:mm but I know it is 22 minutes and 45 secs. The formula gives me 1365 (22*60 plus 45) but I need it to show 23. I can't format the whole data as hh:mm:ss because the above will show 22:45:00 which will give 1365.

So awkward....

  VoG II 11:40 21 May 2007

If you change that time to


then the formula will work correctly.

  VNAM75 11:47 21 May 2007

OK VoG™, thanks. Its just that I have about 50 cells with some in the format like 22:45 which are supposed to be mm:ss but are reading as hh:mm, so I will have to add 00: individually.

Is there a way of doing a find/replace to place a 00: in front of values like above?

  VoG II 11:59 21 May 2007

With the ones that are actually mm:ss then the formula


will give the correct result.

  johnnyrocker 11:59 21 May 2007

do a find for all instances of 22.45 then select replace all with 00:22:45?


  VNAM75 12:00 21 May 2007

Forget the above - my mistake. Excel is showing 22:45 in the cell, but the formula bar is showing the correct 00:22:45. Why does Excel do that?

Anyway, VoG™s formula works perfectly.

  VNAM75 12:02 21 May 2007

Thanks its sorted now. johnny, not all of them are 22:45, but thats irrelevant now.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Awful clip art from 1994 is being tweeted every hour by a bot

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017