To get the difference between two dates, simply subtract one from the
other: Date2 -
Date1. If the number of hours won’t exceed 23 and the number
of minutes or seconds won’t exceed 59, you can use the HOUR, MINUTE
and SECOND formula functions to do the conversion.
HOUR(Date2 -
Date1) returns the number of hours there are between the two
dates.
MINUTE(Date2 -
Date1) returns the number of minutes there are between the two
dates.
SECOND(Date2 -
Date1) returns the number of seconds there are between the two
dates.
These functions are designed to be used together, meaning that the
the HOUR function won’t ever return a number greater than 23 and the
SECOND and MINUTE functions won’t ever return a number greater than
59. For instance, if four hours, three minutes and two seconds have
elapsed between two dates, HOUR(Date2- Date1)
returns 4, MINUTE(Date2 - Date1)
returns 3 and SECOND(Date2 - Date1)
returns 2.
If you’re just interested in the number of seconds that have elapsed
between the two dates, though, the number you’re looking for is
14,584 seconds. (Each minute has 60 seconds and every hour has 60
minutes, meaning that 14,584 equals 4 * 60 * 60 + 3 * 60 +
4).
Similarly, if you’re interested in the number of minutes that have
elapsed between two dates, the number you’re looking for is 243
minutes. (Each hour has 60 minutes, meaning that 243 equals
4 * 60 +
3).
In Calcapp, as in spreadsheets, a date is a so-called sequential serial number. For the purposes of this discussion, we can ignore how days are represented and just be content to note that the sequential serial number 1 equals one day or 24 hours. This means that the number 1/24 represents one hour, 1/24/60 represents one minute and 1/24/60/60 represents one second.
That means that you need to use these formulas if you can’t use the HOUR, MINUTE and SECOND functions:
(Date2 - Date1) *
24 returns the number of hours there are between two dates.
(Date2 - Date1) *
60 * 24 returns the number of minutes there are between two
dates.
(Date2 - Date1) *
60 * 60 * 24 returns the number of seconds there are between
two dates.
(If you’re interested in having apps that perform as speedily as
possible, you may be tempted to write 60 * 60 * 24 as
86400. That
won’t actually improve performance, as Calcapp automatically does
this optimization before your app is run. This is known as constant
folding. By typing 60 * 60 * 24 instead of
86400, your
formulas will be easier to read.)
Do you want to share a tip with other Calcapp users through this blog? Let us know!