Formula issue: duration field

[Emily_Gonzales]

Using the solution i found in a similar thread but this formula keeps returning an error. I can’t figure out where it has gone awry. The Total Time field is field type: duration. The others are multiple select. Without the DATETIME FORMAT i dont get an error but get the time as raw seconds and that isn’t helpful. Help! what i am doing wrong?

{Team member}&" -"&{Type of Service}&" - "& DATETIME_FORMAT({Total Time},‘h:mm’)

[Mohamed_Swella1]

Hi @Emily_Gonzales,

You are using curly quotation marks in the DateTime_Format, it should be straight. ’ instead of ‘

If this works, please mark this as Solution so others can see it.

BR,
Mo

[kuovonne · Accepted solution]

You cannot use the DATETIME_FORMAT function to format by itself to format a duration as a string. The DATETIME_FORMAT function expects a date object as its input. Notice that in the thread you referenced, the original poster never reported back that the function worked.

Here is a workaround. It adds the duration to a date and then formats that date to show only the hours and minutes. Note that this formula will only work if there are less than 24 hours.

{Team member}&" -"&{Type of Service}&" - " &
IF( 
  {duration},
  DATETIME_FORMAT(
    DATEADD(TODAY(),{duration}, 's'), 
    'HH:mm'
  )
)

[Emily_Gonzales]

What if the duration is longer than 24 hours? is there a way to add the duration to the string? There is no formatting function for time without adding the date stuff?

[kuovonne]

Durations longer than 24 hours don’t work because days only have 24 hours in them. If you want to format longer durations, you need a different formula for the unit conversions.

IF(LEN({Duration} & "") > 0,
  INT({Duration} / 3600) & ":" & 
  IF((MOD({Duration}, 3600) / 60) < 10,"0") &
  MOD({Duration}, 3600) / 60
)

[Emily_Gonzales]

So just so i’m clear. There is no way to include the duration type field in a string formula and format it to ‘hh:mm’ without 1st converting it to a date.

[kuovonne]

My second formula formats a duration field as a string in hh:mm format without converting it to a date.

[M_k]

Hi @kuovonne

I am also looking for a similar formula.

Will your second formula work if I want to schedule reminders?

I have some paying subscriptions every month and I want to automatically have the due dates calculated.

Ideally I would like to have a way to have a formula give me the next due date (30 days), in advance.

This will be for less then ten subscription due dates.

Then I will incorporate it with an Integromat scenario, that I can create with an email reminder system.

I am trying to create something that’s “hands free” or as much as possible, since I don’t want to keep adding new due dates each month.

I hope I am clear.

Thank you,
Mary

[kuovonne]

@M_k The formulas above format a duration as a string, so they are not appropriate for scheduling reminders. In order to schedule a reminder, you need a date/time object.

To have a formula field calculate a date 30 days in the future, use the DATE_ADD formula.

DATEADD(Date, 30, 'days')

To have it calculate a date 1 month in the future, use ‘months’ instead of ‘days’.

DATEADD(Date, 1, 'months')

[M_k]

Hi @kuovonne

Sorry about the confusion.

The first post was from the general forum and the second was the PM (I thought this would be approved quicker), but it was the other way around.

I wanted to ask if the formula from this previous post, that you were kind to help with, could be be used for capturing duplicate records? As part of the solution, could a check mark be automatically added to the duplicate record, but leave one record without the check mark, and then use that as part of the formula to catch all duplicate records?

Thank you for your help.

I realize you are busy.

Thank you,
Mary

P.S. I am using the Free Plan.

Can A Script Filter Records In A Base? Scripting app

Hi Airtable! I thought I would repost this again, since filters did not do the job. Maybe a script might work? I am not sure if this will cost something. I am trying to filter, only records that has one or more fields that are empty. I have created a share link to a copy of the database, if anyone wants to take a look. (https://airtable.com/shrIVQ1d9ckYOpEuV) [image] COPY 1 CSV FACE VALUE 1. GENERAL STAMPS CE1 TO 1708aii GOOD 1 copy copy -… Explore the “COPY 1 CSV FACE VALUE 1. GENERAL …