VA code to get the data horizontally along with some calculations

[Balajibenz]

Hi Team,

I have input sheet with employees time entries.

It has the columns as Company, ID, Name and time entries which are aligned date wise and total break time for each employee in below format.

In/out time would be recorded each and every time when an employee swipes the card for in or out. Break time is total break time for the day.

Company	ID	Name	Date	In/Out Time	Break Time
Solid	2967	Ritz, Vivian	31.01.22	13:09	0.5​
Solid	2967	Ritz, Vivian	31.01.22	16:06	0.5​
Solid	2967	Ritz, Vivian	31.01.22	16:11	0.5​
Solid	2967	Ritz, Vivian	31.01.22	21:58	0.5​
Studitemps	1354	Solihin, Martin Chr.	02.02.22	07:26	0.88​
Studitemps	1354	Solihin, Martin Chr.	02.02.22	13:18	0.88​
Studitemps	1354	Solihin, Martin Chr.	02.02.22	14:11	0.88​
Studitemps	1354	Solihin, Martin Chr.	02.02.22	16:48	0.88​

what i am looking for is the output in the below format where we are having single line of data and respective dates data should be places under it accordingly. If an employee doesn't have date for any particular date we can it as blank or 0 in the respective fields.

Start Time

End Time

Total Working Hours

Total Break

Actual Working Hours

Start Time

End Time

Total Working Hours

Total Break

Actual Working Hours

Company

ID

Name

31-01-2022

01-02-2022

Solid

2967

Ritz, Vivian

13:09

21:58

8.82​

0.5​

8.32​

00:00​

00:00​

0.0​

0.0​

0.0​

Studitemps

1354

Solihin, Martin Chr.

00:00​

00:00​

0.0​

0.0​

0.0​

07:26

16:48

9.37​

0.88​

8.48​

Date column will should be merged for all the 5 data.

Start Time = Least in/out time from the source
End Time = Highest in/out time from the source
Total Working Hrs = =( End Time - Start Time)*24
Break Time = Break Time from the source
Actual Working Hrs = Total Working Hrs - Break Time

This out put should be created as separate worksheet in the same workbook.

Note: Source will have weekly data so it typically has dats for 5 working days. Would be great if at all there is way to do it using arrays or any other formulas rather than VBA code.

Thank you in advance.