Combining with concatenate with formatting for time and duration

[copal]

I’d like to combine three fields from three columns (Start date and time, Description, Duration). However I’m not aware of a way to format the "Start date and time’ so that it displays in a more user friendly format - I’m getting “2018-07-01T12:00:00.000Z “ I’d like to have “2018-07-01 T 1.00pm” for some reason it’s also saying 12pm not 1pm.
This screenshot also shows the setup.

The duration is also a problem displayed as seconds I think /60 /60 give 2.5 and I’d like it displayed as 2:30 or 2h 30m.

I need to bring these into one column with a concatenate so that on another linked table I can rollup this information when I allocate multiple items/rows to a given invoice, so that I have all the ‘Start date and time’, Description and Durations rolled up for a given invoice.

Thanks

[Ptt_Pch]

Well, I’ve got a partial solution for you problem :slightly_smiling_face:
(I’m stuck on the duration as I’ve never played with that yet :winking_face: )

For your difference between your original {Star Date & Time} and the result you get in your formula, you might want to check the format of your field and see if checking/unchecking “use the same time zone (GMT) for all collaborators” solve the problem :winking_face:

If not (or if you don’t want to use the “same GMT for all collaborators”), you’ll probably need to add a SET_TIMEZONE() to the formula.

Using a Time Field included in a Date Field can add a level of difficulty/complexity when re-using it elsewhere.
That’s why I used a DATETIME_FORMAT() function in the formula I tried and seems to be near what you want :winking_face: .
I used an & though instead of CONCATENATE()

' -- '&DATETIME_FORMAT({Star Date & Time},'YYYY-MM-DD')&' T '&DATETIME_FORMAT({Star Date & Time},'LT')&'\n -- '&{Description}&'\n -- '&{Duration}

For the {Duration}, Effectively {Duration}/3600 (as the concatenation of the duration field appears to be in seconds) give 2,5 as result… And I’m actually kind of stuck there (sorry) :confused:

---

Add-on :

I’ve searched the community a little bit for the {Duration} field.

The easiest solution I’ve found would be to create 2 Number fields formatted as integer :

• {Hours} : for the hours in {Duration}
• {Minutes} : for the minutes…

After that, it’s pretty easy to integrate those 2 fields in the formula :winking_face:

' -- '&DATETIME_FORMAT({Star Date & Time},'YYYY-MM-DD')&' T '&DATETIME_FORMAT({Star Date & Time},'LT')&'\n -- '&{Description}&'\n -- '&{Hours}&'h '&{Minutes}&'m'

[W_Vann_Hall]

You format date and time fields using a function with the clever mnemonic of DATETIME_FORMAT() :winking_face: . The precise format is determined by the format specifier used.

For the duration, as far as I know, you’ll have to hand-craft a formatting routine yourself. Duration fields are stored internally and expressed in formulas as seconds, so a formula to display a duration in '#h ##m' format would be

IF(
    {Duration},
    IF(
        {Duration}>=3600,
        INT(
            {Duration}/3600
            )&'h '
        )&
    REPT(
        '0',
        2-LEN(
            (INT(
                MOD(
                    {Duration},
                    3600
                    )/60
                ))&''
            )
        )&INT(
            MOD(
                {Duration},
                3600
                )/60
            )&'m'
    )

(That gives you one or more hour digits followed by 'h' and a space, followed by [always] two minute digits [left-padded with '0' for values less than 10] followed by 'm'.)

[Ptt_Pch]

Thank you very much @W_Vann_Hall for the {Duration} :slightly_smiling_face: !
I was really curious about the formula for that, as I couldn’t get near there no matter what I tried :sweat:

Just in case, in its entirety with the big help of @W_Vann_Hall :tada: , the final formula should look like something like this then :blush:

' -- '&
	DATETIME_FORMAT(
		{Start Date & Time},
			'YYYY-MM-DD')
		&' T '&
		DATETIME_FORMAT(
			{Start Date & Time},
				'LT')
		&'\n -- '&
			{Description}
	&'\n -- '&
IF(
    {Duration},
    IF(
        {Duration}>=3600,
        INT(
            {Duration}/3600
            )&'h '
        )&
    REPT(
        '0',
        2-LEN(
            (INT(
                MOD(
                    {Duration},
                    3600
                    )/60
                ))&''
            )
        )&INT(
            MOD(
                {Duration},
                3600
                )/60
            )&'m'
    )

[copal]

This is great! - thank you so much @Ptt_Pch and @W_Vann_Hall. Thanks for both of the detailed replies, so helpful.
The use same time zone GMT fixed the 1hr time shift issue. The formula also displays the date as I need.
The formula to get the duration works a treat, so much logic to follow there.
Thanks for putting it all together too.

Both so helpful. Many Thanks

[W_Vann_Hall]

@Ptt_Pch :raised_hand: High five!

[Ptt_Pch]

Ahahah :rofl: !
High five @W_Vann_Hall :raised_hand: !
(I didn’t do much though :winking_face: but I learned a lot with your formula :slightly_smiling_face: )

@copal I’m glad to know I could help a little and that this works for you :winking_face: !

[copal]

Big help @W_Vann_Hall & @Ptt_Pch - Thanks Again! :thumbs_up:

[Brett_Snelgrove]

Thanks - this is brilliant and solution I have been looking for. How would this look if expanded to include seconds as well as hours and minutes? I’m trying to decipher it but having trouble.