Time Function

[VENKYS]

Dear All

USING VLOOKUP I HAVE EXTRACTED CERTAIN DATA, WHICH NOW SHOWS AS BELOW

...recd_time...... log_time..........proc_time.......reproc_time.......diff
1:00:00 AM...........................................................................
12:45:00PM......13:50:00 PM....14:50:00 PM...............................
13:50:00 PM.....14:45:00 PM....16:30:00 PM...............................

what i want is

A) that in event proc_time is > 16:30:00 PM the reproc time should be shown as 09:30:00 AM
B) else proc_time should be displayed as reproc_time
C) if log_time is not mentioned then recd_time should be reproc_time
D) next i want to calculate the DIFF

So, my final output will be something in line with as below

...recd_time...... log_time..........proc_time.......reproc_time.......diff.....
1:00:00 AM.................................................1:00:00 AM................
12:50:00PM......13:50:00 PM....14:50:00 PM....14:50:00 PM......2:00:00.
13:30:00 PM.....14:45:00 PM....16:30:00 PM....09:30:00 AM.....20:00:00.


How do i proceed.

Kindly help... thanks in advance.... venky

 

[VENKYS]

Any help ?

 

[Aladin Akyurek]

Dear All

USING VLOOKUP I HAVE EXTRACTED CERTAIN DATA, WHICH NOW SHOWS AS BELOW

...recd_time...... log_time..........proc_time.......reproc_time.......diff
1:00:00 AM...........................................................................
12:45:00PM......13:50:00 PM....14:50:00 PM...............................
13:50:00 PM.....14:45:00 PM....16:30:00 PM...............................

what i want is

A) that in event proc_time is > 16:30:00 PM the reproc time should be shown as 09:30:00 AM
B) else proc_time should be displayed as reproc_time
C) if log_time is not mentioned then recd_time should be reproc_time
D) next i want to calculate the DIFF

So, my final output will be something in line with as below

...recd_time...... log_time..........proc_time.......reproc_time.......diff.....
1:00:00 AM.................................................1:00:00 AM................
12:50:00PM......13:50:00 PM....14:50:00 PM....14:50:00 PM......2:00:00.
13:30:00 PM.....14:45:00 PM....16:30:00 PM....09:30:00 AM.....20:00:00.


How do i proceed.

Kindly help... thanks in advance.... venky

Let A1:E4 house the contents of the exhibit you posted.

D2, just enter and copy down:

=IF(B2="",A2,IF(C2>TIME(16,30,0),TIME(9,30,0),C2))

E2, just enter and copy down:

=IF(D2=A2,"",D2-A2+(D2 < A2))

Custom format E2 as [h]:mm.

 

[VENKYS]

Thanks Chief... you made my job easier....

 

[Aladin Akyurek]

Thanks Chief... you made my job easier....

You are welcome. Thanks for providing feedback.