Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Lorenzo Lee   on Sep 10 In MS Office Category.

  
Question Answered By: Wendy Harrison   on Sep 10

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))))

Share: 

 

This Question has 9 more answer(s). View Complete Question Thread

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


Tagged: