Need Help with Reformatting/Organizing ADP Timecard Reports

[the_wiggler]

Hello hello! Hope you're having a wondermazing day, whomever might be reading this.

The long and short of it is thusly-- I've got some ADP timecard reports in Excel (that were likely just ripped from a PDF) that're formatted in such a way that they're impossible to play with, filter for specific information, etc. I'd like to fix this. Maybe with some sort of VSTACK shenanigans or the like? Some sort of spillable-formula? I've only barely scratched the surface of what's possible with this program, so I'm not even really sure where to start. It'd be best if I had some formula or other method of automating this process.

Here is what they currently look like (sensitive information obviously changed/removed):

atcja1.png

Here's what I'd like them to look like:

atcja2.png

Attached to this post is a workbook-- I did the first 'block' of entries manually, as an example. Also, if anyone's got an idea but there is some sort of 'manual' or 'by hand' tweaking I have to do first to make some automatic solution work, I'm open to that, too. Really, I'm open to any help at all!

Thanks in advance.

[ranman256]

each date cell should be Date/Time
PunchIN : 5/18/22 11:00 am
PunchOut : 5/18/22 2:00 pm

now date math can be performed : cell(punchout) - cell(punchIN)
no need to separate 1 date & 1 time cell.

having a cell with TEXT: "punchIn & Punchout" is useless in calculations.

[the_wiggler]

Sure, fair enough. So what would be a quick, efficient, or automated way of converting the current data in TEXT form to date/time values instead? If not, having it in text is fine as I could maybe convert it and then separate it out later, as really (at least at the moment) I'm most concerned with compiling the data from a handful of spreadsheets so I can see what the 'range' of dates employees worked for is looking like. Happy to take suggestions or make alterations using the great minds here, though, of course.

[JeteMc]

The following formulas may be replaceable by those using Excel 365 functions, but these produce the desired results and at least get the thread moving again.
Note that attempting to remove the blank row (19) is difficult due to merged cells below that row.
1. For Date: =IFERROR(INDEX(D$10:D$48,MATCH(0,INDEX(COUNTIF(AD$9:AD9,D$10:D$48),,),)),"")
2. For First Name: =IF(AD10="","",G$9)
3. For Last Name: =IF(AD10="","",A$9)
4. For Punch In & Out 1: =IFERROR(INDEX($F$10:$F$48,MATCH(AD10,D$10:D$48,0)),"")
5. For Punch In & Out 2: =IFERROR(INDEX($F$10:$F$48,AGGREGATE(14,6,(ROW($D$10:$D$48)-ROW($D$9))/($D$10:$D$48=AD10)/(COUNTIFS($D$10:$D$48,AD10)=2),1)),"")
6. For Special Markers: =IFERROR(INDEX($H$10:$H$48,AGGREGATE(14,6,(ROW($D$10:$D$48)-ROW($D$9))/($D$10:$D$48=AD10)/($H$10:$H$48="LP"),1)),"")
Note that the ranges will need to be changed as modeled for the Timecard Report starting in row 52.
Let us know if you have any questions.