Problem with TimeFormat

Hi Thomas:
With your times in A1 and B1, in C1 enter:

=(B1-A1)*24 and format this cell as General will return 1.5

FYI: Excel treats time as a part of a 24 hour day, hence the result of your formula B1-A1 needs to be multiplied by 24 to convert to hours/parts of hour.

To test, try this: In D1 enter =A1*24 and format as General or Number w/2 decimals. The result will be 10.75 (which is, of course, 10 hours and 45 minutes as a decimal). Also, try changing the format of B1 to Number and see: 0.51 (which shows that 12:15 PM is just past half of a 24 hour day.

HTH

I am hoping we are not losing something in international formatting/translation. My assumption is that A1 and B1 are entered as Excel Time Format, e.g. 10:45 AM and 12:15 PM.

If that is not the case, let me know just what 10,45 means to you. (In US, we use a colon ':' for time separator). If I enter 10,45 in a cell, it gets treated as text. If, to you, the comma represents a decimal (i.e. 10 and 45/100), then that will not be treated as Time to Excel, and what you ask cannot be accomplished with my method. And, yes, 12.15 - 10.45 = 1.7 * 24 = 40.8

You could try this formula to convert your entry to hours/parts of hours:

=LEFT(B1,2)+(RIGHT(B1,2)/60) will return 12.25 (i.e. 12 and 25/100) for 12:15 (or should I say 12,15)

Is this helping? Sorry if I am confusing the issue for you.

Let me know if I am on the right track here.

Thomas: check out this thread for some VBA code to simplify time entry:

http://www.excelforum.com/showthread.php?t=354367


then substitute this code in the appropriate spot for that posted on the above thread: (this code is for HH:MM:SS the posted code is for MM:SS only)

If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select

HTH

Bruce