Time - 0.25 - 15 mins

[Mark Solesbury]

Hey

Is there a formula that can convert hours in decimal into real time?

I need to convert say 2.25 hours into 2 Hours 15 Mins...

Any ideas?

 

[Dave Peterson]

Divide by 24 and format the result as hh:mm

=a1/24
(and format nicely)

 

[Dave Peterson]

Ps.

If you want to update in place, you can:
put 24 in an empty cell
edit|copy that cell
select the range to fix
edit|paste special|check divide
Format that selected range the way you like
clear the cell with 24 in it.

 

[Sandy Mann]

In an empty cellenter 24m then copy it. Select the cell(s) that you want to
convert, right-click and select Paste Special > Divide then re-format the
cell as time.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[*alan*]

As Dave Peterson had suggested:
"If you want to update in place, you can:
put 24 in an empty cell
edit|copy that cell
select the range to fix
edit|paste special|check divide
Format that selected range the way you like
clear the cell with 24 in it."

And, if you actually want to spell out "Hours" and "Mins" as indicated in
your post, you could custom format:
[h]" Hours "mm" Mins"

--alan

 

[Dav]

Hi Mark

Here is my proposal :

with your value in A1
=TEXT(A1/24;"hh \Hour\s mm \Mi\n\s")

Greetings from France

Dav

 

[*alan*]

I see three problems with your formula, one of which is easily fixed:

1. It doesn't work using a semi-colon. Change the semi-colon to a comma
(i.e. =TEXT(A1/24,"hh \Hour\s mm \Mi\n\s") , and it will work. (Perhaps
there's a difference between American and French versions?)

2. If you've got time entries greater than 23.99, it's not going to work.
(24.00 becomes 00 Hours 00 Mins, and 25.75 would be expressed as 01 Hours 45
Mins.

3. Having in effect changed the entries to Text, you've lost the ability to
calculate.

--alan

 

[Dav]

Hi Alan

1. Well yes in French it works with semi-colon and not comma. I'll know it
for the next time.

2. 3. I agree with you with the two other points.

It depends also what does he want to do with it.
It seemed to me a simple and easy solution.

Greetings from France

Dav

 

[*alan*]

You're right. If he has entries of 23.99 and under, and doesn't need to
calculate, it's a great solution.
I very much like the idea of formatting being part of the formula.
--alan

Hi Alan

1. Well yes in French it works with semi-colon and not comma. I'll know it
for the next time.

2. 3. I agree with you with the two other points.

It depends also what does he want to do with it.
It seemed to me a simple and easy solution.

Greetings from France

Dav

 

[Hassan Rasheed]

Hi,
Can any one help me for
I need to convert 7.50 to 7 hours and 50 min
or 0.50, 0.45 into 50 minutes, 45 minutes respectively
I'm waiting for the positive response