I have a cell with the value 02:37:12I need it rounding to the nearest value in minutes ie. 120+37=157I have tried all the formatting options I can think of.
With the time in A1=ROUND(A1*1440,0)and format the result cell as Number.
Thanks VoG™. Just what was needed.
Pressed resolved too quick! I have another value22:45It 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....
If you change that time to00:22:45then the formula will work correctly.
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?
With the ones that are actually mm:ss then the formula=ROUND(A1*24,0)will give the correct result.
do a find for all instances of 22.45 then select replace all with 00:22:45?johnny
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.
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.
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
© Copyright 2017 IDG UK. All Rights Reserved.