• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to organize excel table from raw data?

Dennie

New Member
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!
 

Attachments

  • FingerPrint Report.xlsx
    22.7 KB · Views: 7

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.
 

Attachments

  • FingerPrint Report.xlsb
    24.3 KB · Views: 4

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!
 

Attachments

  • FingerPrint Report - Copy.xlsb
    14.5 KB · Views: 1

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.
 

Attachments

  • FingerPrint Report - Copy.xlsb
    15.3 KB · Views: 2

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!
 

Attachments

  • FingerPrint Report - Copy (1).xlsb
    15.7 KB · Views: 3

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!
 

Attachments

  • FingerPrint Report.xlsb
    35.5 KB · Views: 4

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.
Screenshot 2024-06-15 at 20.22.14.png
 

Attachments

  • FingerPrint Report.xlsb
    55.3 KB · Views: 5
Back
Top