Skip to main content

DATETIME_DIFF problem

  • June 24, 2020
  • 3 replies
  • 63 views
J
Forum|alt.badge.img+10

I’m trying to return minutes difference between a field EXP_RETURN and ACT_RETURN in a formula field RET_DIFF. Formula is: DATETIME_DIFF({ACT_RETURN},{EXP_RETURN},‘MINUTES’)

The EXP_RETURN field is a formula: DATEADD({OUT AT}, RIGHT(DURATION,4),‘minutes’). This field adds a number of minutes to OUT_AT to calculate the EST_RETURN result.

ACT_RETURN is an entered date and time field.

OUT_AT is an entered date and time field.

But my result between a calculate EXP_RETURN of 6/23/2020 8:00PM and ACT_RETURN of 6/23/2020 7:48PM should be 12 minutes, however the result is 0:04 (RET_DIFF formula field is formatted as a duration).

Stumped and any help would be helpful.

    3 replies

    Justin_Barrett
    Forum|alt.badge.img+21

    An important thing to note is that duration fields operate internally on seconds, not minutes. To properly represent 12 minutes, you either need to multiply the formula output by 60, or change DATETIME_DIFF() to output seconds instead. I’d go for the latter.

    I also had to reverse the date fields in your DATETIME_DIFF() function, as it was returning a negative number.

    With both of those changes applied, the formula becomes:

    DATETIME_DIFF({EXP_RETURN}, {ACT_RETURN}, "seconds")

    This creates the expected output when the field is formatted as a hh:mm duration.

      J
      Forum|alt.badge.img+10
      • J_Allen
      • Author
      • Known Participant
      • June 28, 2020

      An important thing to note is that duration fields operate internally on seconds, not minutes. To properly represent 12 minutes, you either need to multiply the formula output by 60, or change DATETIME_DIFF() to output seconds instead. I’d go for the latter.

      I also had to reverse the date fields in your DATETIME_DIFF() function, as it was returning a negative number.

      With both of those changes applied, the formula becomes:

      DATETIME_DIFF({EXP_RETURN}, {ACT_RETURN}, "seconds")

      This creates the expected output when the field is formatted as a hh:mm duration.


      Justin, thanks. So in your example this works, but in my implementation, it still results in a wrong result. See graphic below.

      So I am calculating {EXP RETURN} by selecting a value for duration (field name only) using the following formula: DATEADD({OUT AT}, RIGHT(DURATION,4),‘minutes’).

      For my users, this is visually easy allowing them to select a value of 1H-0060 for a one hour rental for example. So unlike your model of my issue, my {EXP RETURN} field is calculation, not a date field that you entered directly. Maybe there is a better way of selecting a duration (duration of a rental in my case), but seems this should work since it calucates {EXP RETURN} just fine.

      Here is what my results show … so on the first record, I could believe the differences is 4 minutes and 4 seconds, but then the next record should be a difference of 29 minutes, not 3:31. I’ve included a few of my DURATION field values as well.

      Here is my DURATION field options (sample of a few)

      Justin_Barrett
      Forum|alt.badge.img+21

      Justin, thanks. So in your example this works, but in my implementation, it still results in a wrong result. See graphic below.

      So I am calculating {EXP RETURN} by selecting a value for duration (field name only) using the following formula: DATEADD({OUT AT}, RIGHT(DURATION,4),‘minutes’).

      For my users, this is visually easy allowing them to select a value of 1H-0060 for a one hour rental for example. So unlike your model of my issue, my {EXP RETURN} field is calculation, not a date field that you entered directly. Maybe there is a better way of selecting a duration (duration of a rental in my case), but seems this should work since it calucates {EXP RETURN} just fine.

      Here is what my results show … so on the first record, I could believe the differences is 4 minutes and 4 seconds, but then the next record should be a difference of 29 minutes, not 3:31. I’ve included a few of my DURATION field values as well.

      Here is my DURATION field options (sample of a few)


      Here’s what I get when I recreate your setup:

      Looking at your sample, the -4:04 in {RET_DIFF} for the first record isn’t 4 minutes and 4 seconds. It’s 4 hours and 4 minutes. The default duration display format is hours and minutes, not minutes and seconds. That means the calculated value is 4 hours off from what you expect. Now look at the next number, -3:31. If you add 4 hours to that, you get a positive 29 minutes, which is the actual difference. Finally, -3:47 plus 4 hours equals a positive 13-minute difference, which is the actual difference. In other words, all of your calculations are 4 hours off.

      One of your date fields is formatting the values based on GMT. If the formatting is GMT, all of the dates that you enter based on your local time will actually be shifted based on the difference between your time zone and GMT.

      After doing a quick test, I’m going to guess that it’s the {OUT AT} field that’s to blame, and I’m also going to guess that you are somewhere in the Eastern time zone. :slightly_smiling_face: Turn off the “Use the same time zone (GMT) for all collaborators” option at the bottom of that field’s settings, and I’m pretty sure you’ll see the correct difference values.

        Terms & ConditionsAccessibility statement