• Learn
  • Blog
  • Pricing
  • About
  • Sign in

Tip: Get the number of hours, minutes or seconds between two dates

To get the number of hours, minutes or seconds between two dates, subtract one date from the other and use the HOUR, MINUTE or SECOND formula functions. HOUR won't return a number greater than 23 and MINUTE and SECOND won't return a number greater than 59. Read on to learn how to fix this.

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!

« Bug report: Asked to reload your app again and again? Feature: Better process for updating shared apps »