Subtracting military time without a colon in Google Sheets
Liking the look of military time with no colons (0130), I chose to use that time format on a time-tracking Google Sheet. It’s quickly obvious that finding the duration of the "in" and "out" times wouldn't be a simple subtraction formula.
In: 0130, Out: 12:45
1245 - 130 ?
Our time system is based on units of 60 minutes and 24 hours, and not 10s like our counting system (big up to the ancient Egyptians and the Dozenal Society of America). So Sheets of course takes my 0130 number as 130 units—not 1 hour and 30 minutes. What I needed to do was extract the minutes, which are in the 1s and 10s decimal places (30 and 45 in our in and out times example). So let’s divide the numbers down into units of 100, and 1s and 10s should be our remainders—our minutes.
A modulo operator MOD(dividend, divisor) returns the remainder after a division operation. So MOD(0130,100) returns 30. MOD(1245,100) = 45. MOD(0100,100) = 0. And so on. Now that we can extract the minutes, we can use them to find and then subtract the whole hours:
Find the remainder of out time and subtract it from out time.
1245 - MOD(1245,100) = 1200
Find the remainder of in time and subtract it from in time.
130 - MOD(130,100) = 100
1200 - 100 = 1100
Represents hours duration (11h).
1100 * 0.6 = 660
Duration converted to minutes.
Then add the out time remainder, which would be the minutes. And subtract the in time remainder, which would be the minutes of the in time hour that we do not want to include in the calculation of the duration.
660 + mod(1245,100) - mod(130,100) = 675 minutes
The final formula to subtract military time in Google Sheets, where A2 is in time and B2 out: