Formula issue: duration field

Emily_Gonzales · ‎Jul 31, 2020

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

kuovonne · ‎Aug 01, 2020

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

See Solution in Thread

Mohamed_Swella1 · ‎Jul 31, 2020

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 · ‎Aug 01, 2020

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 · ‎Aug 03, 2020

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 · ‎Aug 03, 2020

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 · ‎Aug 03, 2020

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 · ‎Aug 03, 2020

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

M_k · ‎Aug 03, 2020

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 · ‎Aug 03, 2020

@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 · ‎Aug 06, 2020

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.