Calculate the difference between two dates
  • 17 Feb 2023
  • 1 Minute to read
  • Dark
    Light

Calculate the difference between two dates

  • Dark
    Light

Article Summary

Finding the difference between two dates can be helpful for many workflows. Using the DATETIME_DIFF() function in the example below, we'll find the difference - in days - between two dates. Note that "Payment Received" and "Invoice Billed" are the exact names of our two columns below:

DATETIME_DIFF(
  {Payment Received},
  {Invoice Billed},
  'days'
)

360056111474ScreenShot2020-01-27at52513PM.jpg
Another common use of DATETIME_DIFF() is to find the difference between a record's date and today's date. You can use DATETIME_DIFF() in combination with the NOW() formula to dynamically find the difference from today's date.

DATETIME_DIFF(
  NOW(),
  {Invoice Billed},
  'days'
)

1500011460402now.png

For another example, you can also use DATETIME_DIFF to find someone's age when given their birthday:

DATETIME_DIFF(
  {Birth Date},
  NOW(),
  'years'
) * -1

4415804352791birthdate.jpg

Displaying in decimal format

In another example, if you need to measure the time an employee spent working each day, you could create two date field to track their clock in and clock out times. Using a DATETIME_DIFF() function like the following, you can display the total hours worked for the day.

DATETIME_DIFF({Clock Out},{Clock In},'minutes')  / 60

360045331974ScreenShot2019-09-04at60843PM.jpg

Displaying in duration format

Since duration fields store values in seconds, you can tweak the formula to output the DATETIME_DIFF() value in seconds instead of minutes.  That way, if we format it as a duration using h:mm, we get the expected result.

DATETIME_DIFF({Clock Out},{Clock In},'seconds') 

360046189453ScreenShot2019-09-04at61129PM.jpg



Was this article helpful?