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

 

[BSALV]

just round or roundup or rounddown ???

Map1
	A	B	C	D
1	2/01/1900 5:03:31		05:03:31
2			05:04:00	1440
Blad2

Cell Formulas
Range	Formula
C1	C1	=A1-TRUNC(A1)
C2	C2	=MROUND(C1,1/1440)
D2	D2	=24*60

 

[etaf]

ignore
answered as i went to put an xl2bb
is this showing as text
change format to General do you get 2.2104
if so , just change the format to TIME
if its TEXT
then in a new cell
=TIMEVALUE(RIGHT(A1,8))

 

[Fluff]

If the value is a proper date time, then just format the cell to [m]:ss

 

[Mr2017]

just round or roundup or rounddown ???

Map1
	A	B	C	D
1	2/01/1900 5:03:31		05:03:31
2			05:04:00	1440
Blad2

Cell Formulas
Range	Formula
C1	C1	=A1-TRUNC(A1)
C2	C2	=MROUND(C1,1/1440)
D2	D2	=24*60

Round up, please.....

 

[BSALV]

also with fluff's solution, but i didn't see you wanted to get ride of the hours ?

Map1
	A	B	C	D
1	2/01/1900 5:03:31		05:03:31
2			05:04:00	1440
3
4		fluff	00:03:31
5			00:04:00
6
7		other method	00:03:31
8			00:04:00
Blad2

Cell Formulas
Range	Formula
C1	C1	=A1-TRUNC(A1)
C2,C5	C2	=CEILING.MATH(C1,1/1440)
D2	D2	=24*60
C4	C4	=TIMEVALUE(TEXT(A1,"\0\0:mm:ss"))
C7	C7	=TIME(0,MINUTE(A1),SECOND(A1))
C8	C8	=TIME(0,MINUTE(A1)+(SECOND(A1)<>0),0)

 

[Fluff]

My take on this.

+Fluff 1.xlsm
	A	B	C	D
1	02/01/1900 05:03:31		3183:31	3184:00
Data

Cell Formulas
Range	Formula
C1	C1	=TEXT(A1,"[m]:ss")
D1	D1	=CEILING.MATH(A1+0,1/1440)

 

[Mr2017]

If the value is a proper date time, then just format the cell to [m]:ss

It's a proper date and time and but is a log of how long a call was ie 5 hours and 3 mins. But when I format it to Custom: mm:ss ([m]:ss wasn't available?) it displays it as 03:00 ie 3 mins and 00 seconds.

Do you know why that is? I thought it would be 303 mins? ie 5 * 60 + 3....

 

[BSALV]

Map1
	A	B
1	2/01/1901 5:03	05:03:31
2		05:04:00
Blad1

Cell Formulas
Range	Formula
B1	B1	=TIMEVALUE(TEXT(A1,"uu:mm:ss"))
B2	B2	=CEILING.MATH(B1,1/1440)

error in the translation "uu:mm:ss" (dutch) ----> "hh:mm:ss" (english)

 

[Fluff]

But when I format it to Custom: mm:ss ([m]:ss wasn't available?)

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?