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 ?

 

[JvdV]

Wouldn't it work for you to do something along the lines of:

=B3-B2

And, if the auto-formatting does not pick this up as time by itself, use a number format yourself: u:mm

 

[robertvdb]

no this doesn't work properly, because for input reasons, B2 and B3 are formatted as Custom 0\:00. So eg 11:05 - 10:00 would give 0:65

 

[JvdV]

Ah, right, I see what you did there. Makes no sense to me the input isn't actually timevalues, but in C6 then use:

=(B3-B2)/24

And then use the appropriate number formatting.

 

[robertvdb]

seems to work. Thanks !

 

[robertvdb]

I am reopening this because I'm still having issues. I am enclosing a new example.

 

[shinigamilight]

What is the value without formatting you need to show that, because a simple c3 - b3 would get you your answer.

 

[robertvdb]

if I do C3-B3 , and I format the result as Custom hh:mm, then I get 00:00 instead of 0:10

 

[James006]

Hi,

have you tested for your custom format: h:mm ?

 

[robertvdb]

I still get 0