1

I have a tab-separated file, e.g.,

0018803 01  1710    2050    002571      
0018951 01  1934    2525    003277  
0019362 02  2404    2415    002829  
0019392 01  2621    2820    001924  
0019542 01  2208    2413    003434  
0019583 01  1815    2134    002971   

The 3rd and 4th columns represent Start Time and End Time in HHMM format. If the Start Time or End Time passes midnight, it keeps incrementing the hours.

I want column 6 to show elapsed time of (Column 4 minus Column 3) in hours and minutes.

Column 6 result in this sample would be 3:40, 5:51, 00:11, 1:59, 2:05.

  • 1
    What unit is are the numbers in column 3 and 4 using? – Kusalananda Mar 20 at 10:17
  • @RoVo Well, in that case lines 2, 3, 4 and 5 are weird. – Kusalananda Mar 20 at 10:18
  • there is one java program through that this time is converted to this format.. there is no particular unit defined for col3 and col4 – Aditya Mar 20 at 10:38
  • 1
    If a person used to active on internet from 2300 hr to next day 200hour. it will reflect as 2600 – Aditya Mar 20 at 10:43
  • 1
    are columns 3 and 4 guaranteed to be 4 characters long? 123 -> 0123 – Dalvenjia Mar 20 at 18:48
0

Hi this assumes the start time and end time will always be 4 characters long:

awk 'NF {hrs = substr($4, 1, 2) - substr($3, 1, 2); min = substr($4, 3, 4) - substr($3, 3, 4); if(min<0) {hrs--; min = 60 + min}; printf "%s\t%02d:%02d\n", $0, hrs, min}'

Explanation:

NF: Number of Fields, this is to skip empty lines

hrs = substr($4, 1, 2) - substr($3, 1, 2);: Get the first 2 characters of column 4 and subtract the first 2 characters of column 3 and assign to variable hrs;

min = substr($4, 3, 4) - substr($3, 3, 4);: Repeat the last logic with the last 2 characters for the minutes;

if(min<0) {hrs--; min = 60 + min};: If minutes are negative, subtract 1 from hours and assign the result of adding 60 to that negative number to variable min;

printf "%s\t%02d:%02d\n", $0, hrs, min: Lastly print all the line and the variables hrs and min zero padded

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.