Comparing Timestamps

[HoneyLemonDew]

We have a system that we are using to generate login and logout times throughout the day (that you can export to excel), and we also have a manual excel tracker that we use to input the time we logged out and logged back in and the reason we logged out at that time. Now, the official report is the one that the system generates and I need to include in that report why we logged out during the times that are indicated on the system report that will be based on the manual excel tracker. The problem is more often than not, the manual excel tracker has different timestamps versus the timestamps that are being recorded and generated by the system. How do I compare the two reports so I can get the reason of logout from the manual excel tracker?


Here's a sample workbook: https://drive.google.com/open?id=1LCub_QYaGK7Mu_E_lbuercsU8Bjuw6Dg

I haven't really tried anything because I am a newbie and I have absolutely no idea what to do

Please help

 

[bobsan42]

It is absolutely normal to have two different independent systems to make time records at different moments in time.

Try this, after sorting out data issue described below the formulas:
on sheet Manual Tracker in column H for all records put the following formula:

=MAX(MATCH($D:$D,'system report'!$D:$D,1),MATCH($D:$D,'system report'!$C:$C,1))

Then on sheet System report in column F or G or whatever put the following formula for all records:

=IFERROR(INDEX('manual tracker'!$G:$H,MATCH(ROW(),'manual tracker'!H:H,0),1),"")

However for this to work the StartTime and EndTime values in both sheets must be formatted in the same way and be actual time values.
Currently they are not the same - on System report the format is AM/PM.
On the other sheet the times are 12 hours earlier. It is mixed up somehow.
Sort this out before trying to match any values. The other way is to include additional columns for converting the values somehow and use the new columns for matching.

 

[Fluff]

Cross posted https://www.excelguru.ca/forums/showthread.php?9810-Comparing-Timestamps

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

Also posted here
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1214954-comparing-timestamps