Jump to content
  • 0
ronbrumbarger

Total Hours Worked / Datediff

Question

Hello.  I have a timesheet report representing hours worked.  Relevant field names are: StartTime, EndTime and TimeSpan (a calculated field - let Caspio sweat the routine calculations).  I'm struggling with the syntax in the "Aggregate" function entry.  My goal is an output which shows: HH:MM...

For example, if my time entries are: 
StartTime   EndTime  TimeSpan
08:30             10:30         2:00
09:45             10:45         1:00
13:30             15:45         2:15        (sum = 4:15)

If I use: sum(datediff(minutes, StartTime, EndTime ))  I get 255... (4 * 60 + 15)

But how in the world do I present the output as 4:15 and not 255.  Yes, I've changed the 'Formatting' to Time (HH:MM), to no avail. 

Any help would be deeply appreciated!

 

Share this post


Link to post
Share on other sites

17 answers to this question

Recommended Posts

  • 1

I have Timspan as a formula field in my table:

Datediff(minute,[@field:StartTime], [@field:EndTime])

Then on my report I show it as a calculated field to show in the format you wish:

CONVERT(varchar(5),  DATEADD(minute, DATEDIFF(minute, [@field:StartTime], [@field:EndTime]), 0), 114)

image.png.e33c754910e238c9bcd6916b783c6acd.png

 

But in my aggregation I call the minute formula field from table:

CONVERT(char(8), DATEADD(MINUTE, SUM(Timespan), ''), 114)

image.png.deeb27f969fbb3f50d5481ce7f5150a3.png

 

This setting worked for me perfectly:

image.png.afd38dc6632f6e4f66f4f59f3ec5d1e0.png

 

Attached is the sample page

 

CaspioData_2018-Jan-26_0857.zip

Share this post


Link to post
Share on other sites
  • 0

Thank you!  I appreciate your willigness to try to help...  Sadly, the Caspio documentation on these things is thin and the error messages worthless.  So frustrating!  Any chance you would be willing to have a brief phone call with me to resolve this?  I've wasted considerable time trying to untangle this knot.  I'm happy to receive a private message and share my phone number if so...

Share this post


Link to post
Share on other sites
  • 0

Count the total mins either in the table or as cal field on your report. Here we call it TimeSpan

 

Datediff(minute,[@field:StartTime], [@field:EndTime])

 

Then in the aggregation use that to get the total:

 

CAST ((SUM(TimeSpan)/60) AS VARCHAR(6)) + ':' +CAST ((SUM(TimeSpan)%60) AS VARCHAR(2))

Share this post


Link to post
Share on other sites
  • 0

Hi,

You can also try use the following formulas:

To calculate the hours worked:

CAST(DATEDIFF(second, [@field:Start_Time],[@field:End_Time]) / 60 / 60 % 24 AS NVARCHAR) +':'+ CAST(DATEDIFF(second, [@field:Start_Time],[@field:End_Time]) / 60 % 60 AS NVARCHAR)

 

For the total hours, insert the following on your aggregation field:

CONVERT(varchar(5), DATEADD(minute, SUM([@calcfield:6]), 0), 114)

For calc field6:

DATEDIFF(minute, [@field:Start_Time],[@field:End_Time])

You may click on this sample DataPage for your reference. 

Test credentials

Email: test@caspio.com

Pass: 1234

 

-JolliBeng

Share this post


Link to post
Share on other sites
  • 0

I have tweaked the formula provided by @MayMusic further since some time I need zero in front of single-digit hour e.g. 7:00.

So, for the total&aggeration field it looks like this:

(CASE 

WHEN LEN(CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:End_time]))/3600) AS VARCHAR(6))) < 2 

THEN '0'+ CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:Hora_fin]))/3600) AS VARCHAR(6))

ELSE CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:Hora_fin]))/3600) AS VARCHAR(6))

END)
+ 
':'
+
(CASE 

WHEN LEN(CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:Hora_fin]))%3600/60) AS VARCHAR(6))) < 2 

THEN '0'+ CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:Hora_fin]))%3600/60) AS VARCHAR(6))

ELSE CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:Hora_fin]))%3600/60) AS VARCHAR(6))

END)

Hope it would help someone.

Regards,

vitalikssssss

 

Share this post


Link to post
Share on other sites
  • 0

Hi @SonoftheSun

You will need to cast/convert the result of the Aggregation into a text if you want them to be concatenated.

Example:

'Total: ' + CAST((SUM([@field:Income])) as NVARCHAR) + ' || Expected: ' + CAST((SUM([@field:Target])) as NVARCHAR)

 I hope this helps.

~WatashiwaJin~

Share this post


Link to post
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...