• 0

# Converting a total number to hours/minutes

## Question

Hello.  I have an aggregate function on a datapage to sum up all minutes keyed into a number field.  I need to figure out how to convert that total number into hours/minutes.  For example if the total number is 75 minutes I can divide that by 60 and get 1.25 but I'd rather it say 1.15 or 1 hour and 15 minutes.  Thanks for any help.

## Recommended Posts

• 0

Hello @Joemac,

1) You may test this formula:

`CAST( CAST((SELECT SUM(field_name) FROM Table_name) AS int) / 60 AS nvarchar) + ':'  + right('00' + CAST(CAST((SELECT SUM(field_name) FROM Table_name) AS int) % 60 AS nvarchar(2)),2) + ' Hrs' `

The output is, for example:

0:20 Hrs

1:25 Hrs

2) Another option is:

```CASE

WHEN (SELECT SUM(field_name) FROM Table_name) < 60

THEN CAST((SELECT SUM(field_name) FROM Table_name) as nvarchar) + ' minutes'

WHEN (SELECT SUM(field_name) FROM Table_name) >= 60 AND (SELECT SUM(field_name) FROM Table_name) <1440

THEN CAST( CAST((SELECT SUM(field_name) FROM Table_name) AS int) / 60 AS nvarchar) + ' hours '  + right('00' + CAST(CAST((SELECT SUM(field_name) FROM Table_name) AS int) % 60 AS nvarchar(2)),2) + ' minutes'

END```

The output is, for example:

50 minutes

3 hours 05 minutes

Hope this helps.

##### Share on other sites

• 0

@CoopperBackpack - thanks for your help - I used the first one and it works!  However it doesn't subtotal the groups (see below).  Any idea on how to fix this?

##### Share on other sites

• 0

Hi @Joemac,

I am sorry for the late reply.

As far as I know,  custom formulas are not applicable for Subtotals (applicable only for Totals  )

This is why you see the same result as Total and as Subtotals. This is the expected behavior at the moment.

As an option, you may suggest new features  here http://ideabox.caspio.com/

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.