Converting hh:mm:ss to minutes in excel

  VNAM75 11:03 21 May 07
Locked

I have a cell with the value

02:37:12

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 07

With the time in A1

=ROUND(A1*1440,0)

and format the result cell as Number.

  VNAM75 11:14 21 May 07

Thanks VoG™. Just what was needed.

  conrail 11:21 21 May 07

bump

  VNAM75 11:32 21 May 07

Pressed resolved too quick! I have another value

22:45

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 07

If you change that time to

00:22:45

then the formula will work correctly.

  VNAM75 11:47 21 May 07

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 07

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

=ROUND(A1*24,0)

will give the correct result.

  johnnyrocker 11:59 21 May 07

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


johnny

  VNAM75 12:00 21 May 07

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 07

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.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Prehistoric Britain is laid out in these Royal Mail stamp illustrations

Best running headphones | Best sport & fitness headphones: 4 brilliant pairs of wireless…