DATETIME_DIFF - two different formats, not working

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.

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'
  )
)

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

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.

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'
  )
)

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?

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.