How to create a proper excel table from unorganized data?

[Sochen]

Hi All,

I have the report data which is recording staff fingerprint in and out. However, that report is not organize properly as it did not show whether the staff comes to work late or come back from break-time late.
So I want to make the excel table which will calculate the staff who will come to work late from the unorganized data table as below:

I am not sure if there is any possible formula or data transform or VBA to get it done.
Thank you so much!

Data -1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2								Shift	From	To
3								Morning	08:30	17:30
4								Evening	13:00	22:00
5
6	Table 1: Raw data from system							Table 2: Result
7													Break time (1 hour)
8	Name	Clock Log	ID	Remark	Location			Date	Name	ID	Location	Time In	Time Out	Time In2	Time Out3	Total Working hours	Late (in mn)	Late Time after break (in mn)
9	Russell	01/05/2024 08:26	2222		CC			01-May-24	Russell	2222	CC	8:36	13:21	14:18	17:32	8:56	6.00	0.00
10	Russell	01/05/2024 13:21	2222		CC			05-May-24	Russell	2222	CC	12:49	16:21	17:18	22:15	9:26	0.00	0.00
11	Russell	01/05/2024 14:18	2222		CC			06-May-24	Russell	2222	CC	8:16	13:23	14:31	17:34	9:18	0.00	8.00
12	Russell	01/05/2024 14:28	2222		CC			07-May-24	Russell	2222	CC	8:02	13:18	14:21	17:41	9:39	0.00	3.00
13	Russell	01/05/2024 17:32	2222		CC
14	Russell	05/05/2024 12:49	2222		CC
15	Russell	05/05/2024 13:12	2222		CC
16	Russell	05/05/2024 16:21	2222		CC
17	Russell	05/05/2024 17:18	2222		CC
18	Russell	05/05/2024 17:48	2222		CC
19	Russell	05/05/2024 22:15	2222		CC
20	Russell	06/05/2024 08:16	2222		CC
21	Russell	06/05/2024 13:23	2222		CC
22	Russell	06/05/2024 14:31	2222		CC
23	Russell	06/05/2024 17:34	2222		CC
24	Russell	07/05/2024 08:02	2222		CC
25	Russell	07/05/2024 08:22	2222		CC
26	Russell	07/05/2024 13:18	2222		CC
27	Russell	07/05/2024 14:21	2222		CC
28	Russell	07/05/2024 14:22	2222		CC
29	Russell	07/05/2024 17:41	2222		CC
Sheet1

Cell Formulas
Range	Formula
P9:P12	P9	=O9-L9
Q9:Q10	Q9	=IF(I3>=L9,0,(HOUR(L9-I3)*60)+MINUTE(L9-I3))
R9:R12	R9	=IF(M9+TIME(0,60,0)>=N9,0,(HOUR(N9-M9)*60)+MINUTE(N9-M9)-60)
Q11	Q11	=IF(I3>=L11,0,(HOUR(L11-I3)*60)+MINUTE(L11-I3))
Q12	Q12	=IF(I3>=L12,0,(HOUR(L12-I3)*60)+MINUTE(L12-I3))

 

[bobsan42]

the data you have is not "unorganized" - it is just a redings log.
However, it lacks important elements to allow for analyzing and summarizing:

1. You need to know and MARK somehow which one is IN and which one's OUT. And to be honest I think the readings you chose for IN/OUT are arbitrary. Even better will be to mark them IN1/OUT1 and IN2/OUT2.
2. What are the readings you have left out (uncolored)? Also normally you should have an even number of records each day (INs = OUTs), but on 01.05.2024 you have five records. So another thing to do to allow for analyzing is to mark faulty records which should not be taken into account.

At least you have to mark all valid INs and OUTs in a helper column.
After this is done you can use complicated formulas, PowerQuery, PivotTables, VBA ... basically anything to make a summary table utilizing the automation tools at your disposal.

 

[Sochen]

the data you have is not "unorganized" - it is just a redings log.
However, it lacks important elements to allow for analyzing and summarizing:

1. You need to know and MARK somehow which one is IN and which one's OUT. And to be honest I think the readings you chose for IN/OUT are arbitrary. Even better will be to mark them IN1/OUT1 and IN2/OUT2.
2. What are the readings you have left out (uncolored)? Also normally you should have an even number of records each day (INs = OUTs), but on 01.05.2024 you have five records. So another thing to do to allow for analyzing is to mark faulty records which should not be taken into account.

At least you have to mark all valid INs and OUTs in a helper column.
After this is done you can use complicated formulas, PowerQuery, PivotTables, VBA ... basically anything to make a summary table utilizing the automation tools at your disposal.

Hello!

Thank you so much for your response!
1. yes, I just randomly select the time record to put into table because we should have only 4 times record 2 times record for report to works and off from work, and another 2 for break times. However, there are possibility that staffs scan more than requirement and system will record base on what they scan. Just like what you have mentioned still can mark them In1/Out1 and Int2/Out2.. And all the data can input into the table without randomly select.
2. Just what I have mentioned in point 1, I only randomly select the correct procedure of the time record because some days will have more than 4 records.

I would highly appreciate it if u could share me the solution.

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

How to organize excel table from raw data?

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

chandoo.org

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Sochen]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

How to organize excel table from raw data?

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

chandoo.org

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Dear Sir,

My sincere apologies.

 

[Burrgogi]

Dear Sir,

My sincere apologies.

I was thinking about creating a spreadsheet to give you some suggestions but then I saw RoryA's comment above. If he had never pointed out that you had cross posted to another forum, I would have wasted all kinds of time coming up with a solution for you. Keep in mind that people like myself volunteer our time here - we do not get paid for any of this. Why should I waste my time replying to your thread when you already have an answer? I hope you understand now why it's important to disclose such things when you post a question here.