Using Modulo Function to Convert Date & Time Parts

It doesn’t take rocket science to know how to convert seconds into minutes, minutes into hours, or hours into days. What is tricky, however, is splitting a single unit of measurable time, such as seconds, into separate units of hours, minutes, and the remaining seconds. That too isn’t rocket science, but it can become quite tedious without the use of the modulo function.

For example, converting seconds directly into hours is pretty straightforward, as you divide the total seconds by 3,600 (60 seconds x 60 minutes gives you 3,600). The FLOOR() function ensures only whole hours are returned:

FLOOR(SUM([Total Seconds Logged]) / 3600)

But now you need to separate or deduct those hours from the total amount in seconds in order to calculate the leftover seconds, which will be converted into minutes. As such, you would first need to take the whole number of hours and multiply it by 3,600 to get the exact amount in seconds, which you will then deduct from the total seconds in order to get the balance.

Alternatively, you could make your life much simpler by using the modulo (%) function.

Modulo divides one number equally by another and outputs whatever is left over. So if you only had 7,400 total seconds, then SUM([Total Seconds Logged]) % 3,600 would be 200, as 3,600 goes into 7,400 twice, leaving a remainder of 200.

Just by using the modulo function, we can bypass the entire process of having to first subtract the hours from the total time, simply by doing the following:

Dividing these results by 60 and wrapping them in another FLOOR() function would give you whole minutes.

Then it’s just a matter of using modulo (%) 60 on the total number of seconds to workout the left-over seconds.

Without the modulo function, you would first need to subtract the whole number of calculated minutes from the total seconds, by first converting those minutes back into seconds. These scenarios are fairly simple, but they help to demonstrate just how underrated the modulo’s ability to shorten calculations really is.