converting time to decimal

Posted by jack on January 14, 2000 4:10 PM

Is there a formula to convert time to decimal?

i.e 693.09(hrs) to 693.15(hrs in dec)

I can do it manually by dividing the minutes by 60
but wondered if there was a formula or command to do this.

any help appreciated

thanx Jack

Posted by Celia on January 16, 2000 12:59 AM

Jack
If the hours/minutes are separated by a decimal point (as in your example), the following formula will convert the hours/minutes(in cell A1) to decimal hours.
But there must surely be a better way of doing it !
Celia

=IF(ISERROR(RIGHT(A18,LEN(A18)-FIND(".",A18)+1)),A18,LEFT(A18,FIND(".",A18)-1)+(ROUND((RIGHT(A18,LEN(A18)-FIND(".",A18)+1)/0.6),2)))

Posted by Celia on January 16, 2000 1:08 AM

CORRECTION
The formula given refers to cell A18 (not cell A1)

Posted by Ivan Moala on January 16, 2000 1:24 AM

or try
=INT(A18)+(A18-INT(A18))/0.6

as another option

Ivan

Posted by Celia on January 16, 2000 1:52 AM

Can also use
=ROUNDDOWN(A18,0)+(A18-ROUNDDOWN(A18,0))/0.6

Celia

Posted by Jack on January 16, 2000 9:40 AM

Thanx 4 the response

The formula works when i input details in A18

But on my sheet A18 has to be in [h]:mm format
and is the sum of 2 times added together using a formula ie a16+a17=a18. If i dont input a18 manually the formula doesn't work.

any ideas

Thanx Jack

Posted by Jack on January 16, 2000 9:43 AM

The formula works when i input details in A18 But on my sheet A18 has to be in [h]:mm format

Posted by Jack on January 16, 2000 10:41 AM

RE:Still need help....my mistake

sorry Celia,Ivan hours and minutes are separated by

Posted by Celia on January 16, 2000 7:25 PM

Re: RE:Still need help....my mistake

Jack
The following formula should work
Celia

=HOUR((VALUE(A18)))+ROUND(MINUTE((VALUE(A18)))/60,2)

Posted by Celia on January 17, 2000 12:20 AM

Re: RE:Still need help....my mistake

Simpler formula :

=HOUR(A18)+MINUTE(A18)/60

Posted by Jack on January 17, 2000 12:34 PM

Re: RE:Still need help....sorry

Sorry it still doesnt work

I am using excel to make a sheet to add total work hours over a continuous period.

I think it is a cell format problem

All my cells are in [h]mm format and times are separated by :

but I need to convert my total in my last column to hours:minutes with the minutes in decimal

if I use =ROUNDDOWN(A18,0)+(A18-ROUNDDOWN(A18,0))/0.6

where a18 is 639:09 the results I get in [h]mm are 707.15 or in number format 29.47

What cell format should i be in?

P.s thanx 4 being patient

Posted by Celia on January 17, 2000 4:19 PM

Re: RE:Still need help....sorry

Jack
If all of your cells are formatted as Time, I do not understand how you can have a total Time cell of 659 hours.
However, if you want to convert from a "time" cell, use =HOUR(A18)+MINUTE(A18)/60
If you want to convert from a "number" cell, use = INT(A18)+(A18-INT(A18))/0.6
Both formulas must be in a "number" cell.
If, you want to get the total hours from cells which are formatted as Time, each cell has to be converted by the hour/minute formula and the result of each added together.
Hope this helps.
Celia

Posted by Jack on January 19, 2000 2:40 PM

Re: ta Celia

Thanx Celia

it appears to work!

thanx again

Jack