Total Hours Worked / Datediff

[ronbrumbarger]

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!

 

[MayMusic]

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)

 

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

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

 

This setting worked for me perfectly:

 

Attached is the sample page

 

CaspioData_2018-Jan-26_0857.zip

[MayMusic]

This is what you can have in aggregation:

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

 

[ronbrumbarger]

Thank you @MayMusic, but after trying several times, I constantly receive an error message. It just can't be this hard...

[MayMusic]

You need to replace Timestamp with the field name where you have your times in. What error are you getting?

 

[ronbrumbarger]

Thank you, @MayMusic

I pretty much copied and pasted your text straight away...  My code is in the graphic attached.

[MayMusic]

Oh I see where the issue is, you should not read the value as parameter here: [@field:Timeentry_TimeSpan] only use the name: Timeentry_TimeSpan

[MayMusic]

You only use parameter format in calculated field in aggregation we call the field name itself.

[ronbrumbarger]

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

[MayMusic]

I think as it shows in screenshot Timentry_TimSpan is a formula field in your table. What is the formula? I can create a example and send it over to you

[ronbrumbarger]

THANK YOU @MayMusic!!!!!

[MayMusic]

You are very welcome. I am glad it worked for you

[Vitalikssssss]

Hi @MayMusic,

I have noticed that this expression does not return a correct result if accumulated hours exceeds 24 hours. 

Can you help me to tweak the formula? 

Thanks,

vitalikssssss

[MayMusic]

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

[JolliBeng]

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

[SonoftheSun]

Hi @JolliBeng your resolution is helpful. Quick question, Is it possible to add/concatenate the expected  total of hours next to the aggregation result? 

(e.g  Total: 120 hours || Expected: 300 Hours)

Thanks for the answer  

[Vitalikssssss]

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

 

[Aether]

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~

[roattw]

Very helpful.  May, What does your data entry page look like?  How can you set a time field (Date/time) where people only enter the time (ie the time they started or ended)?  Without getting a dateor using a calendar popup?

[Vitalikssssss]

On 12/19/2019 at 2:54 PM, roattw said:

Very helpful.  May, What does your data entry page look like?  How can you set a time field (Date/time) where people only enter the time (ie the time they started or ended)?  Without getting a dateor using a calendar popup?

This might help: https://howto.caspio.com/tech-tips-and-articles/common-customizations/separate-input-fields-for-datetime-parts/

Regards,

vitalikssssss

[RuisiHansamu]

Hi, you may also want to consider this formula with HH:MM format : 

CONVERT(VARCHAR,(Datediff(minute,[@field:Start_date],[@field:End_date])/60))

+':'+

CASE WHEN (Datediff(minute,[@field:Start_date],[@field:End_date])%60) < 10 THEN '0'+CONVERT(VARCHAR,(Datediff(minute,[@field:Start_date],[@field:End_date])%60))

ELSE

CONVERT(VARCHAR,(Datediff(minute,[@field:Start_date],[@field:End_date])%60))

END 

 

you may also find this helpful: https://howto.caspio.com/datapages/reports/advanced-reporting/calculated-fields-and-datediff-function/ 

[RuisiHansamu]

There is also a similar and recent update regarding this. Here is the link to that forum post: 

 

[Kurumi]

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:

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/

 

[telly]

hi,

 Just want to add a helpful reference regarding to formula: https://howto.caspio.com/function-reference/#:~:text=Date and Time Functions