Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I have a problem in finding difference of time. The format of time

entered is :

Time in Time out

225500 231600

244200 11600

The last two digits(right most) represent seconds, next two digits

give hours and the left most is hours. It is in 24 hours format (i.e.

1PM means 13) entries have been done as 24 instead of recording as 00

as when entered as number in excel it removes the preceding zeros.

I need to find out the minute and second difference between Time out

and Time in. How is it possible to do that?

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.

Didn't find what you were looking for?
Find more on Finding difference in time
Or get search suggestion and latest updates.

Related Topics:

- Time Difference for Page Creation time
- To calculate the time difference between two days
- Time difference calculations across midnight
- Display character which appears number of time equal half of maximum time
- Add-Ins loaded into Excel
- using find find all
- Difference between vba.round and application.round
- Is there a difference between vba.round and application.round
- Difference between servlet and JSP?
- Differences between Access to MySQL and Microsoft SQL Server
- difference between String and StringBuffer Objects
- May I know the difference between Trusted and non Trusted applet
- difference between java beans and enterprise jb
- Does anyone know the difference between a .class and a .exe file
- ejbCreate() and ejbPostcreate() difference
- Difference between Clone and Refrence
- Difference between .aspx file and .aspx.vb file
- Difference between JNI and CORBA ?
- difference between ArrayList & Vector
- difference between strings, classes, and objects
- Differences between microsft jvm and sun's
- Difference between webserver and java server
- Difference b/w java beans & enterprise java beans
- what is difference between an iterator and Enumeration
- can any body tell me difference between <include action> and include directive tag