Back to Dates in Excel archive index

Back to archive home

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

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

CORRECTION

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

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

or try

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

as another option

Ivan

Can also use

=ROUNDDOWN(A18,0)+(A18-ROUNDDOWN(A18,0))/0.6

Celia

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

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

sorry Celia,Ivan hours and minutes are separated by

Jack

The following formula should work

Celia

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

Simpler formula :

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

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

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

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

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.

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.