Converting time

[Mr2017]

What's the best format to use to convert this time value to minutes and seconds in Excel?

I would then want to round up the time to the nearest minute, in a separate cell.

Does anyone know?

Please assume that this value is in cell A1:

02/01/1900 05:03:00

 

[Mr2017]

You would need to create a custom format & in the Type field type in [m]:ss
However why is it showing a date of 2nd Jan 1900? Not sure why it's showing a date. It's data that I inherited. But thank you for your suggested solution.

 

[Mr2017]

You would need to create a custom format & in the Type field type in [m]:ss
However why is it showing a date of 2nd Jan 1900?

Ok, thanks - I assume this particular custom format rounds the time to the nearest minute, as well, right?

 

[Fluff]

No, it just shows the total number of minutes & seconds. Why not try it & you'll see what you get.

 

[Mr2017]

No, it just shows the total number of minutes & seconds. Why not try it & you'll see what you get.

I had tried it, but all the seconds were zero, so I assumed that it had rounded everything to the nearest minute.

 

[Fluff]

but all the seconds were zero

Maybe your time values do not include any seconds, like the example you posted.

 

[Mr2017]

Maybe your time values do not include any seconds, like the example you posted.

That's quite possible!

 

[Mr2017]

Maybe your time values do not include any seconds, like the example you posted

Maybe your time values do not include any seconds, like the example you posted.

Ok, I have two final questions:

1) I have used the custom format suggested ie [m]:ss

and this time 01:02:00 is converted to 62 mins (which makes sense ie 1 hour and 2 mins is 62 mins)

and this time 02/01/1900 05:03:00 is converted to 3183:00 ie 3,183 minutes, which seems odd?

2) The original raw format of the two times above come up as 0.0430555555555556 and 2.21041666666667, when pasted as values, from a CSV file.

If I then use this formula =TIME(0,MINUTE(B2)+(SECOND(B2)<>0),0) to round them up to the nearest minute (you may need to change the B2 cell reference), I get 02:00 and 03:00, which looks like 2 and 3 minutes, respectively (when the custom [m]:ss formatting is applied in those cells, as well. This seems very odd!

Do you know why 62 mins and 3,183 minutes would be rounded down to 2 and 3 mins, respectively?

 

[Fluff]

02/01/1900 05:03:00 is converted to 3183:00 ie 3,183 minutes, which seems odd?

Not at all, you have 2 days which are 1440 minutes per day which makes 2880 plus the 5:03:00 makes 3183

Do you know why 62 mins and 3,183 minutes would be rounded down to 2 and 3 mins, respectively?

They aren't, neither of those examples have any seconds.

 

[Mr2017]

Not at all, you have 2 days which are 1440 minutes per day which makes 2880 plus the 5:03:00 makes 3183

They aren't, neither of those examples have any seconds.

How do you know this time is 2 days? I'm intrigued....

02/01/1900 05:03:00

 

[Fluff]

Because it's the 2nd of Jan 1900.
Time is just a fraction of a day so when you add times together, if they go over 24hrs, excel adds another day.

+Fluff 1.xlsm
	A
1
2	00/01/1900 20:00
3	00/01/1900 22:00
4	01/01/1900 18:00
Master

Cell Formulas
Range	Formula
A4	A4	=SUM(A2:A3)