Problem with TimeFormat

[Jonsson]

Hi all,

I want to write 10,45 in cell A1 and 12,15 in cell B1 and get the right time (eg. 12,25-10,75=1,5 ) in cell C1 in order to calculate it proparly.

Can that be done in excel?

Thanks in advance!

//Thomas

[swatsp0p]

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

[Jonsson]

Hi,
If I do A1 10,45 and B1 12,15, and C1 B1-A1*24, I get 40,8?
Have I missunderstood what you mean?

//Thomas

[swatsp0p]

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.

[Jonsson]

Hi Bruce,

Thanks for your effort to help me.
And yes, you're on the right track!

FYI: In Sweden we also write 10:45 if we actually want to say 45 minutes past 10.
But, as I think that writing 10:45 is slower than writing 10,45 I hoped someone could help me on to the right trackand still get the right sum.

Now I need to convert this so it works in my environment.

Thanks Bruce!

//Thomas

[swatsp0p]

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

[Jonsson]

Hi Bruce,

I actually already use that code already.

I think that McPearson wrote it, but I'm not sure.........

The problem is that I have 3 cells, start, stop and lunchbreak. Then I have 6 hidden columns calculating diffent conditions from the start, stop and lunchbreak time.

Then I start of on a new day, and so on, for a hole week........
Therefore, it will be a lot of differrent ranges, some to be calculated as decimals, some to be calculated as HH:MM.

So, I have to think about how to do this.....

//Thomas