Adding time (as decimals if possible) into exel

Hi Brett,

It is not clear to me whether you are having the students enter their times as Excel time values (e.g., 4:10 or 4 hours and 10 minutes), or as decimal hours (e.g., 4.10, or 4 hours and 6 minutes). Of course, if the latter you should be able to simply add them up and display the total as decimal hours. But if the former and you want to display the total in decimal hours you have to recognize that internally Excel represents all date-time values in units of days, so that you must multiply by 24 to get a value in hours. So for example if you want to sum the Excel time values in range A1:A10 but display the result not as a time value but as decimal hours, your formula should be

=SUM(A1:A10) * 24

AND you must format this cell not as Time but as a number (e.g., "General").

Does this help?

Damon

Before I started using excel I would use the decimal (e.g. 3.45 to equal 3 hours and 45 minutes) And then I would take the steps to convert the minutes to hours after I added.

I have would put the times into excel like 3:40:00 but that never seems to work right for me. So no, I don't necessarily need to keep it in a decimal form. I just need a way to add up the hours and possibly subtract a few hours off at the end of each list.

By the way if you havn't noticed. I am not an excel expert.

There is a little known Function in the Analysis ToolPak Add-IN (which must be installed) called DOLLARDE that will do this.

In B1, enter
=DOLLARDE(A1,60) enter
Now when a time is entered in A1 (Say 4.45 for four hours and forty-five minutes) B2 will evaluate to 4.75

The reverse Funtion is DOLLARFR

=DOLLARFR(A1,60) will change 4.75 to 4 hrs. 45 mins (You can use custom formatting to include the text)

HTH

lenze

Brett:

Here is a coded use of the DOLLARDE to eliminate the need for a second column or formulas. This assumes you are entering the times in column A. Simply paste this code in the WorkSheet module (Right Click the sheet tab and choose View Code).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Intersect(Target, [A:A]) Is Nothing And Target.Count = 1 Then
Target = Evaluate("=dollarde(" & Target & ",60)")
End If
Application.EnableEvents = True
End Sub

Now, if you enter 3.45 for 3 hrs and 45 mins , it will automatically be entered as 3.75. You can modify the intersect range so the code only applies to the cells you choose. A search of the board for WorkSheet_Change should turn up numerous examples. If you need assistance, repost your details as to what range you need the code to apply to

Also, thanks to MrExcel MVP Yogi Anand for helping me with the nomenclature on this code. I just couldn't get it right.

HTH

lenze