How to organize excel table from raw data?

[Dennie]

Hi Experts,

I have the raw data from the system which is not organize properly in the table. And those data is the time record whether the staff clock in and out. So I want to transform/create the table with calculation. In my attached file, I have 2 tables, which include "Table 1" is the raw data from the system while the "Table 2" is the result of the table after organizing. In the system will record the times that staff clock in and out, if staff clock in and out 4 to 6 time, then it will record/show the total number that they clock in/out. So in the table should record only when they report to work, off work, and break time (in and out).

Could u pls share if there is any possible way to get it done by excel formula or VBA.

And I am using both office 2019 and 2021.

Thank you!

 

[mohamed ilyas]

hi
check this..

 

[Dennie]

hi
check this..

Hello Sir,
It works perfectly.
Thank you!

 

[vletm]

Dennie​

You wrote about ... perfect - okay.
... then ... was Your own results valid?

Was there clear rules, if one day has any other number of data than four?
eg
# if one data per day then when one hour break time?
# is two datas eg 08:30 - 17:50 ... do there have to have break time? ... missing fingerprints or what?
There seems to be datas from one to six per day.
Should it guess - when one hour break time?

I marked some 'wonders' with yellow.
There is none result about Total Working hours, Late(in min) or Late Time after break (in min).
... missing clear rules.

 

[Dennie]

Dennie​

You wrote about ... perfect - okay.
... then ... was Your own results valid?

Was there clear rules, if one day has any other number of data than four?
eg
# if one data per day then when one hour break time?
# is two datas eg 08:30 - 17:50 ... do there have to have break time? ... missing fingerprints or what?
There seems to be datas from one to six per day.
Should it guess - when one hour break time?

I marked some 'wonders' with yellow.
There is none result about Total Working hours, Late(in min) or Late Time after break (in min).
... missing clear rules.

Hello Sir,

Yes, you are right. It's not 100% valid.
I should have given clear example.
I have attached the again the example with remark and rule for the breaktime.

Thank you!

 

[vletm]

Dennie​

Someone has done a long double shift?
Only few has had breaks - really?
Could You verify those P, Q & R -column results?
How to guess ... know, which are doubles?
How to know, which shift?... eg Josh 11/05 ... 12/05.

 

[Dennie]

Dennie​

Someone has done a long double shift?
Only few has had breaks - really?
Could You verify those P, Q & R -column results?
How to guess ... know, which are doubles?
How to know, which shift?... eg Josh 11/05 ... 12/05.

Hello Sir,
- Yes, we have two working shifts as showing in H3 and H4.
- All staffs had break for 1 hour, but some of them forgot to clock in or out that why the data did not show.
- To guess which one is double if the time record showing the gap of first each time record lesser than 1hr or 1hr30mn. Example, first report to work at 8:30am, the clock in again at 9:15AM, then the 9:15AM will record as faulty/double.
- Pls refer to working shift in H3 and H4.
-There is staff who work full shift also which from morning until night (rarely happen)

And I also have attached the file with some explanations and calculation there as well, Sir.

Thank you!

 

[vletm]

Dennie​

I tested something ... with Your reply #4 data.
You could see one sample output.
It's still a sample!
One basic rule ... there will be always all values.
... [im]possible missed values will fill automatic!

 

[vletm]

Dennie​

I found few cases which needs to modify.
.. as well as I added some details, which You should have.
Below is the top of the latest layout.
Press [ Do It ] to refresh result.