# Calculate difference in numbers and save in colon-separated time format with bash script

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

• 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
• 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
• are columns 3 and 4 guaranteed to be 4 characters long? 123 -> 0123 – Dalvenjia Mar 20 at 18:48

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