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
(I’m stuck on the duration as I’ve never played with that yet )

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

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

---

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

' -- '&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() . 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} !
I was really curious about the formula for that, as I couldn’t get near there no matter what I tried

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

' -- '&
	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 High five!

[Ptt_Pch]

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

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

[copal]

Big help @W_Vann_Hall & @Ptt_Pch - Thanks Again!