Best Of

You can create a beast mode formula to convert month wording or abbreviations into datetime, or into month numbers.

When I create ETLs, I typically have a value for numeric month such as month(`transaction_date`) and the alphabetic monthname such as monthname(`transaction_date1). I use the wording for display and the numeric form for sorting.

For what you describe, you would need something like

CASE 

  WHEN `MonthAbbrev` = 'Jan' THEN 1

  WHEN `MonthAbbrev` = 'Feb' THEN 2

  WHEN `MonthAbbrev` = 'Mar' THEN 3

  WHEN `MonthAbbrev` = 'Apr' THEN 4

  WHEN `MonthAbbrev` = 'May' THEN 5

  WHEN `MonthAbbrev` = 'Jun' THEN 6

  WHEN `MonthAbbrev` = 'Jul' THEN 7

  WHEN `MonthAbbrev` = 'Aug' THEN 8

  WHEN `MonthAbbrev` = 'Sep' THEN 9

  WHEN `MonthAbbrev` = 'Oct' THEN 10

  WHEN `MonthAbbrev` = 'Nov' THEN 11

  WHEN `MonthAbbrev` = 'Dec' THEN 12

  ELSE NULL

END

I believe there may also be a string to date formula like

STR_TO_DATE(`DateText`, '%b %d, %Y')

UNIX_TIMESTAMP() in Domo converts a date-and-time value (like "2024-11-04 15:26:00") into the number of seconds since January 1, 1970 00:00 UTC — a continuous numeric timestamp.

We use it because Domo can’t directly subtract two datetime fields and get a difference in seconds. By turning both Created Time and Responded Time into numbers (seconds), we can simply subtract one from the other to find how many seconds apart they are. After that, we convert those seconds into hours, minutes, and seconds for display.

It's a start anyway.

You have to work more on dealing with the values over the weekend. There you'd have partials that I didn't really solve. Full disclosure, I initially thought this was no big deal. Until I started thinking though it.

Here's what I came up with. Starting with sample data.

If I interpret working hours to be hours from 8am to 5pm, then everything else is non-working hours. And I only want to calculate with working hours.

So using my sample values and the first record, ID 1:

From 3:26pm to 5:00pm would be 94 minutes. So we should have 94 minutes on Nov 4. Then another 11 minutes from 8:00am to 8:11am on Nov 5. Or 105 minutes. Subtract 60 for the hour with 45 minutes remainder the answer would be 1h:45m.

WorkSeconds :

CASE

  -- if either timestamp is on weekend (Sunday=1, Saturday=7), return 0

  WHEN DAYOFWEEK(`Created Time`) IN (1,7) OR DAYOFWEEK(`Responded Time`) IN (1,7) THEN 0


  -- same calendar day

  WHEN DATE(`Created Time`) = DATE(`Responded Time`) THEN

    CASE

      -- compute clipped start ts (8:00) or created time

      WHEN

        (CASE WHEN `Created Time` < CONCAT(DATE(`Created Time`),' 08:00:00')

              THEN UNIX_TIMESTAMP(CONCAT(DATE(`Created Time`),' 08:00:00'))

              ELSE UNIX_TIMESTAMP(`Created Time`)

         END)

        >=

        (CASE WHEN `Responded Time` > CONCAT(DATE(`Created Time`),' 17:00:00')

              THEN UNIX_TIMESTAMP(CONCAT(DATE(`Created Time`),' 17:00:00'))

              ELSE UNIX_TIMESTAMP(`Responded Time`)

         END)

      THEN 0

      ELSE

        (CASE WHEN `Responded Time` > CONCAT(DATE(`Created Time`),' 17:00:00')

              THEN UNIX_TIMESTAMP(CONCAT(DATE(`Created Time`),' 17:00:00'))

              ELSE UNIX_TIMESTAMP(`Responded Time`)

         END)

        -

        (CASE WHEN `Created Time` < CONCAT(DATE(`Created Time`),' 08:00:00')

              THEN UNIX_TIMESTAMP(CONCAT(DATE(`Created Time`),' 08:00:00'))

              ELSE UNIX_TIMESTAMP(`Created Time`)

         END)

    END


  -- different calendar days (only handles first day remainder + last day morning)

  ELSE

    -- seconds remaining on the Created date from clipped created→17:00

    (CASE

       WHEN UNIX_TIMESTAMP(CONCAT(DATE(`Created Time`),' 17:00:00'))

            <=

            (CASE WHEN `Created Time` < CONCAT(DATE(`Created Time`),' 08:00:00')

                  THEN UNIX_TIMESTAMP(CONCAT(DATE(`Created Time`),' 08:00:00'))

                  ELSE UNIX_TIMESTAMP(`Created Time`)

             END)

       THEN 0

       ELSE UNIX_TIMESTAMP(CONCAT(DATE(`Created Time`),' 17:00:00'))

            -

            (CASE WHEN `Created Time` < CONCAT(DATE(`Created Time`),' 08:00:00')

                  THEN UNIX_TIMESTAMP(CONCAT(DATE(`Created Time`),' 08:00:00'))

                  ELSE UNIX_TIMESTAMP(`Created Time`)

             END)

     END)

    +

    -- seconds on the Responded date from 08:00→clipped responded

    (CASE

       WHEN (CASE WHEN `Responded Time` > CONCAT(DATE(`Responded Time`),' 17:00:00')

                  THEN UNIX_TIMESTAMP(CONCAT(DATE(`Responded Time`),' 17:00:00'))

                  ELSE UNIX_TIMESTAMP(`Responded Time`)

             END)

            <= UNIX_TIMESTAMP(CONCAT(DATE(`Responded Time`),' 08:00:00'))

       THEN 0

       ELSE (CASE WHEN `Responded Time` > CONCAT(DATE(`Responded Time`),' 17:00:00')

                  THEN UNIX_TIMESTAMP(CONCAT(DATE(`Responded Time`),' 17:00:00'))

                  ELSE UNIX_TIMESTAMP(`Responded Time`)

             END)

            - UNIX_TIMESTAMP(CONCAT(DATE(`Responded Time`),' 08:00:00'))

     END)

END

WorkSeconds_HHmmss :

CONCAT(

  FLOOR(`WorkSeconds` / 3600), ':',

  FLOOR(MOD(`WorkSeconds`, 3600) / 60), ':',

  MOD(`WorkSeconds`, 60)

)