Time format - rounding up/down

[FrecklePaw]

Hi, I have some data with minutes and seconds in this format:

00:00

I need to be able to change the values to round down to the nearest minute from 9 seconds and under and round up to the nearest minute from 10 seconds and over, e.g.

01:09 would equal 1 minute
01:10 would equal 2 minutes

How can I do this easily and quickly? I have a lot of data and so I need a mass replace/edit option.

TIA,
FP

[HansV]

Let's say you have such values in A2 and down.
Enter the following formula in B2:

=MROUND(A2+TIME(0,0,20),TIME(0,1,0))

Format B2 as mm:ss, then fill down.
If you want to replace the original values:
- Select the cells with the formula in column B.
- Paste as Values to column A.
- Delete or clear column B.

[FrecklePaw]

Hi Hans, I used the formula but it gave me this result:

01:42 the formula changed to 0.070833333 and so I replaced it with mins and seconds, but it hasn't rounded up to 2 minutes, i.e. 02:00

Do I need to change something else in the formula or my settings to achieve the result?

Thx

[FrecklePaw]

Let's say you have such values in A2 and down.
Enter the following formula in B2:

=MROUND(A2+TIME(0,0,20),TIME(0,1,0))

Format B2 as mm:ss, then fill down.
If you want to replace the original values:
- Select the cells with the formula in column B.
- Paste as Values to column A.
- Delete or clear column B.

Hi Hans, I used the formula but it gave me this result:

01:42 the formula changed to 0.070833333 and so I replaced it with mins and seconds, but it hasn't rounded up to 2 minutes, i.e. 02:00

Do I need to change something else in the formula or my settings to achieve the result?

Thx

[HansV]

This means that your time 01:42 is not 1 minute and 42 seconds, but 1 hour and 42 minutes.
To enter 1 minute and 42 seconds, you MUST enter it as 00:01:42 (0:1:42 will do too). You can still display it as 01:42 by applying the custom number format mm:ss.

[FrecklePaw]

This means that your time 01:42 is not 1 minute and 42 seconds, but 1 hour and 42 minutes.
To enter 1 minute and 42 seconds, you MUST enter it as 00:01:42 (0:1:42 will do too). You can still display it as 01:42 by applying the custom number format mm:ss.

I have a lot of existing data and so won't be entering the times myself, they simply exist as is as 01:42. How do I change them to 00:01:42 or 0:1:42? And then how do I round these up to the 2 mins that I need?

[HansV]

Use the formula

=MROUND(A2/60+TIME(0,0,20),TIME(0,1,0))

and apply the custom number format mm:ss.

[FrecklePaw]

Use the formula

=MROUND(A2/60+TIME(0,0,20),TIME(0,1,0))

and apply the custom number format mm:ss.

Hi Hans, so sorry I am having difficulty with this. I had not appreciated the custom format option!; however, when I tried selecting this after using the MRound formula it has simply converted:

01:42 into 42:00

I think I've gotten in a mess may have to go back to the beginning and start again - can you talk me through it from the top? Apologies! Thanks for your patience.

[HansV]

It might be easier if you copied a few of the times into a new workbook and attached that (we don't need the original workbook)

[FrecklePaw]

I can do, will prepare something to send.
In the meantime, because the information I am cut and pasting into my spreadsheet is formatted automatically after cut and paste as hh:ss and when I change it to mm:ss it wants to change e.g. 01:42 to 42:00 how can I cut and paste the information in AS mm:ss? Does this make sense? I am so confused, thanks for bearing with me!

[HansV]

Where do the data that you cut and paste come from?

[FrecklePaw]

Where do the data that you cut and paste come from?

It come from an outside source app so it was just a selection on screen cut and paste direct from the app. (i.e. not from a Microsoft product). Does that mean it's not possible to do after all?

[HansV]

Please attach a workbook with some of the values.

[FrecklePaw]

Please attach a workbook with some of the values.

Hi Hans, here is a screenshot of the sheet with the cells and showing that it's been automatically categorised as 'custom' 'hh:mm' (when in fact I need it to be in mm:ss, so I can then do the round up formula).
To clarify, I did not make this change; when pasted in, it was automatically set to this setting. And when I try to change it to mm:ss, e.g. my 00:44 seconds turns into 44:00 mins so this is not the solution! I am struggling to find another way.
Does this help?

hrs and mins.PNG

[snb]

Don't bother on formatting.

Code: Select all

=A1/60+(SECOND(A1/60)>9)/1440-SECOND(A1/60)/86400

or

Code: Select all

=FLOOR.PRECISE(A1/60;1/1440)+(SECOND(A1/60)>9)/1440

[HansV]

What snb says. there is no point in trying to change the number format. You need a formula to convert the value and round it the way you want.

[FrecklePaw]

Don't bother on formatting.

Code: Select all

=A1/60+(SECOND(A1/60)>9)/1440-SECOND(A1/60)/86400

or

Code: Select all

=FLOOR.PRECISE(A1/60;1/1440)+(SECOND(A1/60)>9)/1440

thanks so much, I used the first one and it worked great for what I need - thanks again all for help and advice here.

[HansV]

I'm glad you have a solution. The formula I posted in this post higher up in this thread would have had the same result...

[FrecklePaw]

I'm glad you have a solution. The formula I posted in this post higher up in this thread would have had the same result...

Hi Hans, not sure what I was doing wrong there but I got into a bit of a mess with it! Thank you again for your time here.