Is the second example meant to be 116000?

Assuming it is...

How do you know that 116000 is only the next day or n days ahead?

I just noticed that if the second example *is* 116000, it's not possible
because that would be 120000!!

So .... Is the second example valid at all??

Some logic decision has to be applied to handle a 6 character vs a 5 character
time entry.
Your '244200' entry translates to 12:42AM. Your '11500' entry looks like it is
1:15AM.
Applying the TIME( ) function to translate your 'Entry':

TimeOutput=Time(HoursExpression,MinutesExpression,SecondsExpression)

The logic for the 2 Entry lengths looks like this:
6CharTimeEntry=TIME(LEFT(TimeEntry,2),MID(TimeEntry,3,2),RIGHT(TimeEntry,2))
5CharTimeEntry=TIME(LEFT(TimeEntry,1),MID(TimeEntry,2,2),RIGHT(TimeEntry,2))

The Decision logic as follows:
TimeOutput = If (CharLengthOfTimeEntry=6) then 6CharTimeEntry else
5CharTimeEntry

The final 'Entry' expression looks like this:

TimeOutput=IF(LEN(TimeEntry)=6,TIME(LEFT(TimeEntry,2),MID(TimeEntry,3,2),RIGHT(T\
imeEntry,2)),TIME(LEFT(TimeEntry,1),MID(TimeEntry,2,2),RIGHT(TimeEntry,2)))

If your 'Start' time  is in cell B2, the expression is this:

StartTimeOutput=IF(LEN(B2)=6,TIME(LEFT(B2,2),MID(B2,3,2),RIGHT(B2,2)),TIME(LEFT(\
B2,1),MID(B2,2,2),RIGHT(B2,2)))

If your 'End' time is in cell C2, the expression is this:

StartTimeOutput=IF(LEN(C2)=6,TIME(LEFT(C2,2),MID(C2,3,2),RIGHT(C2,2)),TIME(LEFT(\
C2,1),MID(C2,2,2),RIGHT(C2,2)))

Time Difference will follow this logic:
TimeDifference = EndTime - StartTime

Format the result to Time format  type '13:30:55'.

Final Expression is:

TimeDefference=(IF(LEN(C2)=6,TIME(LEFT(C2,2),MID(C2,3,2),RIGHT(C2,2)),TIME(LEFT(\
C2,1),MID(C2,2,2),RIGHT(C2,2))))-(IF(LEN(B2)=6,TIME(LEFT(B2,2),MID(B2,3,2),RIGHT\
(B2,2)),TIME(LEFT(B2,1),MID(B2,2,2),RIGHT(B2,2))))

Yeah... I think it is interesting. At first I thought it could be done with
sheet functions, putting intermediate results in different columns but that
didn't work for me.

.. IMHO I'm pretty sure Anands' best solution is to write a function. From
your answer I think we are on the same page.

There are 3 bits I reckon... Change the given text to a reconisable
date/time... Do the arithmetic... Change the answer back to a recognisable
format... I think.

Same as you... I also did the seperation into 3 sets of 2 characters but I
used the MID() function for all of them rather than LEFT() MID() RIGHT().

The last number given though "11600" prompted my questions. You've "solved"
them by saying ok.. Drop the leading zero if it's earlier than 10 o-clock
'cause it’s a number!!!!

Brilliant!!! And soooo obvious I of course missed it.

I still don't see how to take account od say... 28 hours difference  though.

I think the best way to approach this would be to convert the number into
seconds then subtract then convert it back to H:M:S or HMS.

You could create two functions HMS2S to do the first two conversions and
s2HMS to do the third

Function HMS2S(sIVal As String) As Long
Dim lSeconds As Long
lSeconds = sIVal Mod 100
lSeconds = lSeconds + (Int(sIVal / 100) Mod 100) * 60
lSeconds = lSeconds + Int(sIVal / 10000) * 3600
hms2s = lSeconds
End Function

Function S2HMS(lSecs As Long) As String
S2HMS = lSecs Mod 60 _
+ (Int(lSecs / 60) Mod 60) * 100 _
+ Int(lSecs / 3600) * 10000
End Function

The functions must be in a module!!!

The entry in the cell is
=S2HMS(HMS2S(B4)-HMS2S(C4))

What happens if the period spans midnight?



I would use one function and pass B4 and C4. Let the function call HMS2S for
both cells and adjust if the value from C4 is less than B4. Of course, this
will not work for time  periods over 24 hrs. In that case, you would have to
indicate the day as part of the time value.

Did you get an answer to this at all??????

I didn't read the earlier posts on this thread, but assuming the sample data
given is in A1:B3 try this:

In C2: =TIMEVALUE(LEFT(A2,LEN(A2)-4)&":"&MID(A2,LEN(A2)-4,2)&":"&RIGHT(A2,2))
In D2: =TIMEVALUE(LEFT(B2,LEN(B2)-4)&":"&MID(B2,LEN(B2)-4,2)&":"&RIGHT(B2,2))
In E2: =D2-C2

Total difference  in minutes (in F2): =(HOUR(E2)*60)+MINUTE(E2)
Seconds portion of difference (in G2): =SECOND(E2)

Copy formulas down as needed. Hope this helps,

Finally I have been able to find a solution. Very close to the answer of Thomas.
Using Left , mid and right functions first convert the number to time  format and
then it's quite easy task.

If you have time  and feel like posting details of your solution, I'm sure
it'll be useful to the group.