Decimal to Minutes

[Gary]

Is there a format or formula to convert a decimal to time? An example would
be .5 would be 30 (minutes), .75 would be 45 (minutes), etc. The Workbook
is relatively large so any method would have to easily apply to thousands of
numbers, versus simply manually converting one cell at a time.



Regards,

Gary

 

[Peo Sjoblom]

It's easier than you think Gary, since 1 is one day in Excel which is 24
hours, so one hour is 1/24

just divide by 24

=A1/24

where A1 holds 0.5

then the important thing is to format the cell with the formula as either
hh:mm:ss or [hh}mm:ss (the latter allows for displaying more than 24 hours)

Same goes for the other way around if you have Excel time format and wants
to calculate an amount using an hourly rate

=A1*24*hourly_rate

here it is equally important to format result as currency or number

 

[*alan*]

Assuming your decimal time is in column A and begins with A1,
custom format column B as [h]:mm
and then in B1 enter =A1/24

..5 should then show as 0:30, 1.75 as 1:45, and so on.

If you want your time to show as 0h 30m and 1h 45m instead,
then custom format column B as [h]"h"" "mm"m"

 

[*alan*]

I think that [hh}mm:ss in your reply below must be a typo.
Perhaps you meant to write [hh]:mm:ss ??
--
Alan

It's easier than you think Gary, since 1 is one day in Excel which is 24
hours, so one hour is 1/24

just divide by 24

=A1/24

where A1 holds 0.5

then the important thing is to format the cell with the formula as either
hh:mm:ss or [hh}mm:ss (the latter allows for displaying more than 24
hours)

Same goes for the other way around if you have Excel time format and wants
to calculate an amount using an hourly rate

=A1*24*hourly_rate

here it is equally important to format result as currency or number



--

Regards,

Peo Sjoblom

Is there a format or formula to convert a decimal to time? An example
would be .5 would be 30 (minutes), .75 would be 45 (minutes), etc. The
Workbook is relatively large so any method would have to easily apply to
thousands of numbers, versus simply manually converting one cell at a
time.



Regards,

Gary

 

[Gary]

Works great,
Thanks Peo...

It's easier than you think Gary, since 1 is one day in Excel which is 24
hours, so one hour is 1/24

just divide by 24

=A1/24

where A1 holds 0.5

then the important thing is to format the cell with the formula as either
hh:mm:ss or [hh}mm:ss (the latter allows for displaying more than 24
hours)

Same goes for the other way around if you have Excel time format and wants
to calculate an amount using an hourly rate

=A1*24*hourly_rate

here it is equally important to format result as currency or number



--

Regards,

Peo Sjoblom

Is there a format or formula to convert a decimal to time? An example
would be .5 would be 30 (minutes), .75 would be 45 (minutes), etc. The
Workbook is relatively large so any method would have to easily apply to
thousands of numbers, versus simply manually converting one cell at a
time.



Regards,

Gary

 

[Peo Sjoblom]

Obviously


Peo

I think that [hh}mm:ss in your reply below must be a typo.
Perhaps you meant to write [hh]:mm:ss ??
--
Alan

It's easier than you think Gary, since 1 is one day in Excel which is 24
hours, so one hour is 1/24

just divide by 24

=A1/24

where A1 holds 0.5

then the important thing is to format the cell with the formula as either
hh:mm:ss or [hh}mm:ss (the latter allows for displaying more than 24
hours)

Same goes for the other way around if you have Excel time format and
wants to calculate an amount using an hourly rate

=A1*24*hourly_rate

here it is equally important to format result as currency or number



--

Regards,

Peo Sjoblom

Is there a format or formula to convert a decimal to time? An example
would be .5 would be 30 (minutes), .75 would be 45 (minutes), etc. The
Workbook is relatively large so any method would have to easily apply to
thousands of numbers, versus simply manually converting one cell at a
time.



Regards,

Gary

 

[Guest]

It depends on what format you want the results. If you simply want .5 to
become 30 then multiply by 60.

However, if you want .5 to be formatted in minutes then it is .5 divided by
24 and then you have to format the cell to minutes.

To apply a multiplication or division to multiple cells, enter a value in
any spare cell and copy it. Select the range where you want it applied and
Paste Special and select Multiply or Divide etc. This operation ignores text
in the paste to selection.

Tip: Make a backup of your workbook before applying these operations.

Regards,

OssieMac