Minutes to decimals

[Kingtriton]

Does anyone know of an easy way to get excel to convert minutes int
decimals? An example would be,
1 min= 0.02
2 min= 0.03
3 min= 0.05
4 min= 0.07
5 min= 0.08
6 min= 0.1
7 min= 0.12

You get the picture. I am trying to find a formula that would conver
a number like 6 hours and 20 minutes (entered into a cell as 6:20) int
a payroll friendly number.
I am really stumped with this one.
Thanks in advance for any assistance.
Kingtrito

 

[Arvi Laanemets]

Hi

P.e. with time in cell A1
=A1*24
and format the cell with formula in it as number

 

[Kingtriton]

I know that by multiplying oh, lets say 50 min's by 0.0167 that I ca
convert it from 50 mins to 0.83 and I can do the opposite by dividing.
I can do this fine when I am only dealing with minuets, my problem lie
in converting hours:mins. An example would be this,
The time clock program says I worked 29.98 hours. How do I conver
that into 29 hours and 59 mins or 29:50?
Thanks

 

[Arvi Laanemets]

Hi

=A1/24
and format as "[h]:mm"
NB! Don't omit squary brackets, or you get 5:58 as result! And 29.98 hrs =
29:58, not 29:59 or 29:50

 

[Kingtriton]

That was a typo

 

[Kingtriton]

That worked great! Only problem is that my version of excel only ha
the option of [h]mm:ss not [h]:mm. This will cause errors when addin
hours. Do you know of a way custom format this?
Thanks again,
Kingtrito

 

[Norman Harker]

Hi Kingtriton!

There will be no errors in adding hours as a result of any formatting
that you might adopt as formatting only affects the way number is
displayed not what is stored.

However, to get the format you require use custom format:

Format > Cells > Custom Format
Type in the required custom format string.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

I have a related problem
I have a number of tables in an excel sheet that are normally only printed on paper. Because of this the time is in my columns is displayed as: ' 113005. It is not possible to calculate anything with this way of displaying the times
Does any one know a way to convert this into 11:30:05 without having to adjust it all manually. I tried to use the existing stuff in excel but that didn't help much. I suppose I have to built some kind of a query but do not know how and where to start. Let me know if you know a way to solve this
Thanks in advance.

 

[Frank Kabel]

Hi
try the following formula in an adjacent column
=TIME(--LEFT(TRIM(A1),2),--MID(TRIM(A1),3,2),--RIGHT(TRIM(A1),2))
and copy this down for all rows. after this you may copy this helper
column and insert the content as 'Values' (goto 'Edit - Paste Special'
to do this) to remove the formulas

 

[Ron Rosenfeld]

I have a related problem:
I have a number of tables in an excel sheet that are normally only printed on paper. Because of this the time is in my columns is displayed as: ' 113005. It is not possible to calculate anything with this way of displaying the times.
Does any one know a way to convert this into 11:30:05 without having to adjust it all manually. I tried to use the existing stuff in excel but that didn't help much. I suppose I have to built some kind of a query but do not know how and where to start. Let me know if you know a way to solve this.
Thanks in advance.

If your number is TEXT, as implied by the leading single quote, then Frank's
formula will work. However, the following will work regardless of whether the
entry is TEXT or a Number. The problem with Frank's formula if your entry is a
number is that the leading 0 will be dropped.

=TIME(INT(A1/10^4),INT(MOD(A1,10^4)/100),MOD(A1,100))


--ron