Converting a number to time with vba

[gjsala]

I have a column in excel, for example column F, that has a number in each cell from a range of 1 through 25 but will very each time the program is executed. I would like to convert the numbers to time for example 3.5 would be converted to 3.3 and then I would like to add up the whole column to give a total number of hours and minutes. Would I use a Right function to convert the number after the "." to minutes? Thanks in advance!

 

[Zathras]

First you need to understand that Excel stores a time value internally as a fractional part of a day. So to use your example, 3.5 hours becomes 3:30 (3 hours & 30 minutes) by simply dividing by 24 (hours per day) and displaying it with one of the Time formats.

3.5 divided by 24 equals .145833333 which you can see by formatting the cell as general. Formatting the cell as time causes the value .145833333 to be displayed as 3:30.

So, if you have 3.5 in cell F2, put the formula =F2/24 in cell G2, format it to Time and copy down.

Finally, you can put in a formula like =SUM(F2:F50) to get the total time. You will need to format it with the custom format
[blue]

[hh]:mm

[/color]
to allow it to display more than 24 hours.