Decimal to Minutes

  • Thread starter Thread starter Gary
  • Start date Start date
G

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
 
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
 
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"
 
I think that [hh}mm:ss in your reply below must be a typo.
Perhaps you meant to write [hh]:mm:ss ??
--
Alan


Peo Sjoblom said:
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





Gary said:
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
Click to expand...
Click to expand...
 
Works great,
Thanks Peo...


Peo Sjoblom said:
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





Gary said:
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
Click to expand...
Click to expand...
 
Obviously


Peo


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


Peo Sjoblom said:
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





Gary said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top