Replace string and find value in column in Batch file

Question

I have file like below, I need to find out that if any employee is "absent OR unknown" or late more than 1 min in both time column.

Col1          col2        col3     HH MM SS        HH MM SS 
sales       Present     George     01:02:00        04:05:00
sales       absent      Linda      00:00:03        00:00:00
Marketing   unknown     James      00:00:00        00:00:00

I am successful to remove ":" but not sure how to put if condition piping echo

(for /f "delims=" %%i in (%files%) do (
        set "line=%%i"
        setlocal enabledelayedexpansion
        set "line=!line::= !" 
      echo(!line!  
       endlocal
    )) 

trying to get output like

George is more than 1 hr and 2 min late
Linda is absent 

Any help is Greatly appreciated.

Answer 1

@ECHO Off
SETLOCAL ENABLEDELAYEDEXPANSION
SET "filename1=q72007068.txt"

(
FOR /f "skip=1delims=" %%e IN (%filename1%) DO (
 SET "reason="
 set "outfile=absorunk"
 FOR %%y IN (%%e) DO (
  FOR %%o IN (absent unknown) DO IF %%o==%%y SET "reason=%%o"
  FOR /f "tokens=1-3delims=:" %%u IN ("%%y") DO (
   IF "%%w"=="" (
    SET "person=%%y" 
    ) ELSE (
     SET /a late=1%%u%%v%%w
     IF NOT DEFINED reason IF !late! gtr 1000100 SET "reason=late %%u:%%v:%%w"&set "outfile=late"
    )
  )
 )
 IF DEFINED reason ECHO !person! is !reason!>>"report_!outfile!.txt"
)
)

GOTO :EOF

%%e receives each full line, skipping the first, from the file.

reason is used as a flag to record the reason the report line is being generated - if it is generated.

%%y receives each element of the line in turn.

If an element is exactly absent or unknown, set reason to that value.

Attempt to tokenise the element into %%u..%%w using : as a delimiter.

If %%w is not set, then this is not a time element, so person will be set to the element value and hence will contain the element before the first time element, which is the person's name.

If %%w is set, form hh:mm:ss as 1hhmmss which is a decimal number which does not have a leading 0. Compare that against 1000100 which is 1 minute calculated on the same basis. if greater, then set reason to "late + the actual time-late.

If, after processing all of the elements, reason has been set then produce a report line detailing the person and reason.

I'll leave the formatting of the time as an exercise for those interested.

--- amended

in the light of the comment, reports sent to report_absorunk.txt for absent or unknown and report_late.txt for late.

Note that data will be appended to the report files should they already exist. The variable outfile is simply initialised to absorunk for each line and changed to late if the reason generated is late. Since reason is only defined if there is a reason to report the current data line, lines that are not reported still remain unreported.