DATETIME_DIFF - two different formats, not working

[Rachael_Castela]

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.

Thanks!

[Jeremy_Oglesby]

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.

[Rachael_Castela]

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

[Jeremy_Oglesby]

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.

[Rachael_Castela]

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?

[Jeremy_Oglesby]

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'

Airtable - Support

Supported format specifiers for DATETIME_FORMAT

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the form "DATETIME_FORMAT(Datetime, 'format s...

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.