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.
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.
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.
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?
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.
