Jump to content
  • 0

Converting a total number to hours/minutes


Joemac

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.

Link to comment
Share on other sites

4 answers to this question

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

Please replace field_name and Table_name with your local names.

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.

 

Link to comment
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/

Link to comment
Share on other sites

  • 0

Hi! Caspio now offers integration with OpenAI, so you can use extensions to leverage AI to update your data based on a prompt. Here's a sample use case to get the total worked hours based on Time In and Time Out fields.


Request:
Calculate the total working hours for the following time entries:


Time In: [@field:Clock_In]
Time Out: [@field:Clock_out]


The format should be HH:MM


Insert 'File a leave' if there are no values in:

Time In: [@field:Clock_In]
Time Out: [@field:Clock_out]

Return only the final result.

Result:

image.png

Change the 'Clock_In' and 'Clock_out' fields to your right field names.

To learn more about Extensions, you can check it here: https://howto.caspio.com/integration/extensions/

 

Link to comment
Share on other sites

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.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...