DATETIME_DIFF - two different formats, not working

I would like to capture three dates:

  1. When a new record was created (Date Added to Accelerator)
  2. When the status in “Approval Status” was changed to either Approved / Not Approved (Date Approval was Made)
  3. The difference in time between those two dates (Approval Duration)

*The format for Approval Duration should be in days and hours. Example “2 days, 3 hours” (Or something like 2 d 3 h, that format isn’t as important as it is to show the amt. of time in days and hours)

My concern is that because I’m using two different functions (Created Time and a Formula) that it’s having trouble computing the difference.

Please let me know if I need to change of of my existing formulas or what I can use for the Approval Duration column to make this formula work.

image

image

image

Thanks!

Hi @Rachael_Castelaz – dates can be tricky to work with, and especially when they get mixed up with strings. I’m pretty sure the DATETIME_FORMAT() function is returning a string, not a proper Date object. This means we need to use DATETIME_PARSE() to turn it back into a Date object for your comparison in the {Approval Duration} field.

After that, I think some conditions and math should do the trick to get you days/hours.

IF(
  AND(
    {Date Approval was Made},
    {Date Added to Accelerator}
  ),
  IF(
    DATETIME_DIFF(
      DATETIME_PARSE({Date Approval was Made}, 'M/DD/YYYY h:mm', 'America/Chicago'),
      {Date Added to Accelerator},
      'hours'
    ) > 23.4,
    ROUND(
      DATETIME_DIFF(
        DATETIME_PARSE({Date Approval was Made}, 'M/DD/YYYY h:mm', 'America/Chicago'),
        {Date Added to Accelerator},
        'hours'
      ) / 24
    )  & ' days '
  ) &
  IF(
    MOD(
      DATETIME_DIFF(
        DATETIME_PARSE({Date Approval was Made}, 'M/DD/YYYY h:mm', 'America/Chicago'),
        {Date Added to Accelerator},
        'hours'
      ),
      24
    ) > 0,
    ROUND(
      MOD(
        DATETIME_DIFF(
          DATETIME_PARSE({Date Approval was Made}, 'M/DD/YYYY h:mm', 'America/Chicago'),
          {Date Added to Accelerator},
          'hours'
        ),
        24
      )
    ) & ' hours'
  )
)

I haven’t tested that yet, but I’ve done something similar to get ‘years / months’ before, so the concept should work. Hopefully I’ve got all my commas and quotes in the right spots. Give that a try and see what it gives you.

Shoot, it doesn’t seem to be returning anything. It did let me enter the formula without errors, but it’s returning any information.

image010.jpg

image011.png

image012.jpg

image013.png

image014.jpg

image015.jpg

image016.jpg

image017.png

image018.png

Looks like my assumption about DATETIME_FORMAT() was wrong…

Try this:

IF(
  AND(
    {Date Approval was Made},
    {Date Added to Accelerator}
  ),
  IF(
    DATETIME_DIFF(
      {Date Approval was Made},
      {Date Added to Accelerator},
      'hours'
    ) > 23.4,
    ROUND(
      DATETIME_DIFF(
        {Date Approval was Made},
        {Date Added to Accelerator},
        'hours'
      ) / 24
    )  & ' days '
  ) &
  IF(
    MOD(
      DATETIME_DIFF(
        {Date Approval was Made},
        {Date Added to Accelerator},
        'hours'
      ),
      24
    ) > 0,
    ROUND(
      MOD(
        DATETIME_DIFF(
          {Date Approval was Made},
          {Date Added to Accelerator},
          'hours'
        ),
        24
      )
    ) & ' hours'
  )
)

I think that should return something, and then we can refine your output from there.

Thank you! It’s so close.

It looks like the number of days are correct, but the hours are off. I assume this could be because the Date Approval was Made doesn’t have AM/PM?

image001.png

image010.jpg

image011.png

image012.jpg

image013.png

image014.jpg

image015.jpg

image016.jpg

image017.png

image018.png

image001.jpg

Yep, I think so. Are you opposed to adding it?

If not, you could just change your format specifier in the {Date Approval was Made} DATETIME_FORMAT() function from this:

'M/DD/YYYY h:mm'

to this:

'LLL'

Looks like even after that, there might still be some adjusting to do to get it to return the correct # of hours. Airtable’s date fields are a little mysterious behind the scenes, and sometimes it’s difficult to get them to agree.