Time - 0.25 - 15 mins


M

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?
 
Ad

Advertisements

D

Dave Peterson

Divide by 24 and format the result as hh:mm

=a1/24
(and format nicely)
 
D

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.
 
S

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
 
A

*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
 
D

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
 
Ad

Advertisements

A

*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
 
D

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
 
A

*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

Dav said:
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
 
Ad

Advertisements

Joined
Dec 6, 2017
Messages
1
Reaction score
0
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 :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top