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