Change time into mm:ss

[FryGirl]

I have some ppt recordings and just need to time the slides with a total.

Example:

Book1
	Q	R	S
2	181162	0:03:01.16	0:03:02
3	11626	0:00:11.63	0:00:12
4	70869	0:01:10.87	0:01:11
5	5354	0:00:05.35	0:00:06
Sheet1

column Q is the number I get from ppt, and column R is what the value is in mm:ss. Column S is what I would like. As you can see, I'm always rounding up.

I'm doing all of this in Excel.

 

[JamesCanale]

MrExcelPlayground2.xlsx
	X	Y
16	milliseconds	m:ss
17	181162	3:02
18	11626	0:12
19	70869	1:11
20	5354	0:06
Sheet17

Cell Formulas
Range	Formula
Y17:Y20	Y17	=X17/1000/24/60/60+0.5/24/60/60

 

[Fluff]

Another option

+Fluff 1.xlsm
	Q	R	S	T
1
2	181162	03:01.16	00:03:02	03:02.00
3	11626	00:11.63	00:00:12	00:12.00
4	70869	01:10.87	00:01:11	01:11.00
5	5354	00:05.35	00:00:06	00:06.00
Main

Cell Formulas
Range	Formula
T2:T5	T2	=CEILING.MATH(R2,1/86400)

 

[Rick Rothstein]

Here is another formula you can try...

=MROUND(TIME(0,0,1+A2/1000),TIME(0,0,1))

 

[Fluff]

I get 04:20, but I didn't think ceiling.math existed in 2010.

 

[FryGirl]

Thanks Rick & James. Those work.

@Fluff, col Q is the value I have that is needing converting

 

[Fluff]

col Q is the value I have that is needing converting

Oops managed to miss that bit.

 

[FryGirl]

No worries. It's solved now.

 

[Rick Rothstein]

I get 04:20, but I didn't think ceiling.math existed in 2010.

You were too fast in picking up my original post... I modified it to remove my comment as I forgot to divide the original value by 1000 for your formula.

I seem to be using XL2016 now but do not know why. I loaded XL365 and that "downgraded" to XL2019 for a short time but then that version downgraded to XL2016 (have no idea why) and now I cannot seem to load XL2019 back again. I was holding off updating my profile until I figured it out and then forgot.

 

[Rick Rothstein]

No worries. It's solved now.

I see you marked my formula as the Answer but, personally, I like Fluff's formula (after being modified slightly) better than mine as it uses only one function call...

=CEILING.MATH(Q2/86400000,1/86400)