How to add a duration to concatenated field

[Kim_Trager1]

I thought this would be quite straight forward, but I’m not having it going for me at all.
I have a Roll-up field that is a duration which I need to add to a formula-field as part of a concatenated text.
I tried:

'Time: ’ & DATETIME_FORMAT({Total Time}, ‘h:mm’)

But can’t get it to work - does anyone know how this could work?

[Mike_McLaughlin]

I don’t see the concatenate function in your example.

So, did you try
CONCATENATE(“Time:”,DATETIME_FORMAT({Total Time}, ‘h:mm’))

Also, I’ll edit this to note that you should re-type the single quotations, because if you copy and paste this, for some reason it doesn’t paste at a normal single quotation and breaks the formula it seems.

[Elias_Gomez_Sai]

Mike_McLaughlin:

I don’t see the concatenate function in your example.

The & character works for concatenating strings too.

Kim_Trager1:

'Time: ’ & DATETIME_FORMAT({Total Time}, ‘h:mm’)

I think that the problem is that a Duration is not a Date (although you can sometimes use as that). I’ve tested just the DATETIME_FORMAT part and it does not work.

You should do something like this or make your own formula: Calculating and formatting a duration of time as H:MM

Maybe just dividing by 3600 to get the hours is enough.

[Elias_Gomez_Sai]

You’ve formatted a Date, not a Duration. She has several Durations summed up, and I’ve tested if you can format that as a Date, and not :grinning_face_with_smiling_eyes:

[Mike_McLaughlin]

ok, trying this again, I think this is what you want, maybe…

DATETIME_FORMAT(DATETIME_PARSE( your formula here))

[Kim_Trager1]

I tried to get it down to hours, but it’s a little too crude for what I need it to do.
Thank you for the link to the tutorial I’ll look into it.

I tried the following

IF({Total Time}=BLANK(),’’,INT({Total Time}/3600) & IF(MOD({Total Time},‘m’,3600)<10, ‘:0’ & MOD({Total Time},‘m’,3600)))

But still gives me an ‘Error’

This part definitely gives me hours
IF({Total Time}=BLANK(),’’,INT({Total Time}/3600)

However this part give me an error - not sure where I’m going wrong.
& IF(MOD({Total Time},‘m’,3600)<10, ‘:0’ & MOD({Total Time},‘m’,3600)))

[Kim_Trager1]

I tried the following but it just comes out with an error.
DATETIME_FORMAT(DATETIME_PARSE({Total Time},‘h:mm’))

[Mike_McLaughlin]

I have a solution, but first to note, the MOD() function doesn’t want the ‘m’, it only needs the input and divisor. Also, this solution would need an additional MOD function if the duration goes longer than 24 hours, as it stands a duration of 27:37 would come out as “Time: 03:37”

first MOD it like this to make text

then some fancy formatting like this to get it back to a date, and format the date to just (24)hours and minutes

this can all be combined into one field, its just broken up for ease of understanding.

(Duration_Field-MOD({Duration_Field},3600))/3600&":"& MOD({Duration_Field},3600)/60

"Time: " & DATETIME_FORMAT(DATETIME_PARSE({ModToText},‘HH:mm’),‘HH:mm’)

[Elias_Gomez_Sai]

2 comments:

• Was not working the formula that I linked?
• Why do you @Kim_Trager1 need the 'Time: ’ string? It’s implicit and in the field name.

[Kim_Trager1]

I didn’t think - as per usual, a little time away and a fresh view sorts everything out.

Basically, the error was I use MOD() the same way as DATETIME_DIFF()
So when I changed MOD({Total Time},‘m’,3600) into MOD({Total Time}, 3600))/60 it all worked out as the same way as the tutorial you linked to.

Thanks again.