Merge and Compile Data

Hello:

Please refer to attachced sheet.

Sheet2 is the employee ClockIn-Clockout info
Each day will have 4 columns and data will start from Cell O46

I want to compile Clockin-ClockOut data in Sheet3 by merging Clockin-ClockOut field together.

As an example I have manually pasted 3 days for Employee "Rojee"

Either VB Macro or formual would work for me.

Please let me know if any questions.

Thanks in advance for your help.


Riz

Hello:

I am not sure what you mean.

Please refer to earlier attached workbook.

Riz

I think what scott meant was that instead of entering likes like you did...
938...1720
you should be entering them as...
9:38...17:20

excel will then recognize them as times, and excel is able to work with times pretty easily. It treats all times as fractions/decimals of a day (of 1), so that, even if a cell displays 08:00 am, excel actually "sees" it as 0.33. 06:00 is 0.25, 18:00 is 0.7 and so on

There is no misunderstanding, and I did open your file

On sheet2 P48 you have 938. A assume this is 38 minutes past 9?

If so, then it needs to be entered as 9:38

However, to answer your question, in sheet3 C2, use this, copied down and across...

=IFERROR(INDEX(Sheet2!$O$46:$AL$78,MATCH(C$1,OFFSET(Sheet2!$O$47,-1,MATCH(Sheet3!$A2,Sheet2!$O$47:$AL$47,0)-1,15,1),0),MATCH($A2,Sheet2!$O$47:$AL$47,0)+1)&"-"&INDEX(Sheet2!$O$46:$AL$78,MATCH(C$1,OFFSET(Sheet2!$O$47,-1,MATCH(Sheet3!$A2,Sheet2!$O$47:$AL$47,0)-1,15,1),0),MATCH($A2,Sheet2!$O$47:$AL$47,0)+2),"")

Happy to help and thanks for the feedback