Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home



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


Re: converting time to decimal

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)))


Re: converting time to decimal

Posted by Celia on January 16, 2000 1:08 AM
CORRECTION
The formula given refers to cell A18 (not cell A1)

Re: converting time to decimal

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

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

as another option

Ivan


Re: converting time to decimal

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


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

Celia



Re: converting time to decimal

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


Re: converting time to decimal

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


RE:Still need help....my mistake

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

sorry Celia,Ivan hours and minutes are separated by


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

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

Jack
The following formula should work
Celia

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


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

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

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


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

Posted by Jack on January 17, 2000 12:34 PM
Sorry it still doesn’t 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


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

Posted by Celia on January 17, 2000 4:19 PM
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

Re: ta Celia

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

it appears to work!

thanx again

Jack




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.