display 0,45 as 0:45

[robertvdb]

I have a sheet which calculates time differences.

So 10:45 minus 10:00 equals 0:45

But my spreadsheet shows 0,45 (European format, so US would be 0.45)

How can I display 0:45 ?

 

[James006]

Hi,

With 14.23, The formula should give you as a result: 0.599305555555556

Step1 Watch out for your parentheses .... =(INT(B3)/24)+(B3-INT(B3))/14.4
Step2 Format the result with the Custom Format h:mm

 

[robertvdb]

Hi,

With 14.23, The formula should give you as a result: 0.599305555555556

Step1 Watch out for your parentheses .... =(INT(B3)/24)+(B3-INT(B3))/14.4
Step2 Format the result with the Custom Format h:mm

yes, I missed on the parentheses. Problem solved and thanks for your support and patience !

 

[James006]

Glad to hear you have managed to fix your issue

 

[awoohaw]

Okay, try this number (date) format:
:[m]
Here is what I get:

Mr Excel Questions 4.xlsm
	A	B	C	D	E	F
1	Working Hours	Start	End	timelapse
2					ColC - ColB	Value of ColE
3	Monday	11-Feb-23 10:00:00	11-Feb-23 10:45:00	0.03125	:45	0.031250
4	Tuesday	11-Feb-23 23:30:00	12-Feb-23 01:15:00	0.072917	:105	0.072917
5				SUM:	:150	0.104167
Sheet2

Cell Formulas
Range	Formula
E3:E4	E3	=C3-B3
F3:F5	F3	=E3
E5	E5	=SUM(E3:E4)
D3:D4	D3	=C3-B3

 

[James006]

@awoohaw

OP' s actual problem is : Once you type in a cell the number 14.23 ... how do you convert to it the Time 14:23

 

[awoohaw]

@awoohaw

OP' s actual problem is : Once you type in a cell the number 14.23 ... how do you convert to it the Time 14:23

He has his answer that you provided. But you are incorrect with what you just said.

OP is doing Date/Time Subtraction and wants it displayed in minutes with a colon. If he changed the requirement I missed it.
And I have no idea if my :[h] post will work in his regional location.

to wit, from Post #1:
"I have a sheet which calculates time differences.

So 10:45 minus 10:00 equals 0:45

But my spreadsheet shows 0,45 (European format, so US would be 0.45)

How can I display 0:45 ?"

 

[James006]

@awoohaw

Feel totally free about your own interpretation

 

[awoohaw]

@awoohaw

Feel totally free about your own interpretation

I quoted, not interpreted.

 

[James006]

I quoted, not interpreted.

Enjoy