Calcapp makes it easy to display times and dates, but what about durations? Imagine that we want our users to enter two times and display the difference between them. If the user enters 1:00 pm in the first field and 2:30 pm in the second field, the result field should display “1:30”, indicating that one hour and 30 minutes have elapsed from the time represented by the first field to the time represented by the second field. If the user enters 3:00 pm in the first field and 2:55 pm in the second field, we want the result field to display “-0:05”, indicating that negative five minutes have elapsed.
There is no built-in way to achieve this, but you can get the same results using formulas. In order to do this, we need to understand how dates and times are represented internally in Calcapp. Here’s some information from our guide on dates:
Dates and times are represented as numbers and use the same format as popular spreadsheets. The integer part of the number represents the number of days that have elapsed since December 30, 1899, and the fractional part represents the time of day. The number 10.75 thus represents the 9th of January, 1900, at 6:00 pm (18:00). (0.75 means that three quarters of a day have elapsed.) Such numbers are known as sequential serial numbers.
Calculating the difference between two sequential serial numbers (and thus between two times) is easy: just subtract one number from the other number, and you’re left with the difference.
Presenting the duration is harder. We need to calculate the number of elapsed hours and the number of elapsed minutes separately and then join these two numbers together with a colon (so that the duration reads “1:30”, for instance).
Here’s the formula we use to calculate the number of elapsed hours (associated with a hidden field named Hours):
Remember that a sequentual serial number equaling one represents one full day. As a result, 1/24 represents one hour, as there are 24 hours in a day. To extract the number of hours from the duration, we multiply it by 24. To account for the vagaries of the floating-point numbers used by Calcapp and all major spreadsheets, we round the number to six decimal places. The number of hours will equal 1.5 if the duration is 1:30, meaning that the calculated value isn’t rounded to whole hours.
Here’s the formula we use to calculate the number of elapsed minutes (associated with a hidden field named Minutes):
From the fractional part of the Hours field, we extract the number of elapsed minutes. We remove the fractional part from the Hours field using the TRUNC formula function and subtract it from the number of hours, thereby extracting only the fractional part. That fractional part is then multiplied by 60 to extract the number of elapsed minutes.
Here’s a first attempt at combining the number of elapsed hours and the number of elapsed minutes into one text string to be presented to the user:
This formula is associated with a text field. The & operator joins
together the two text strings with a colon. To remove the fractional
part from the number of elapsed hours, we again use the TRUNC formula
function. (We don’t want to use the ROUND function, as the number
1.75, representing one hour and 45 minutes, would then display as
“2:45” and not “1:45”.) As we’re not displaying the number of elapsed
seconds, we use the ROUND function to display the number of elapsed
minutes.
This formula mostly works, but doesn’t take negative durations into account. A negative duration should be displayed as “-1:45” and not “-1:-45”, so we use the ABS formula function on the number of elapsed minutes:
Finally, the number of minutes is customarily formatted as two numbers for display purposes. This can be achieved by displaying “0” before the number if it is less than ten.
This is the final formula:
Do you want to share a tip with other Calcapp users through this blog? Let us know!